Extract a Text String to remove unwanted text
I have 2 reports that I have to compare. In one report, I get the text string I need to compare, but with extra text appended to it. How can I use a RIGHT or LEFT to extract only the needed text when it is of variable length?
Example
textstring1don't want this
textstringtextstring1don't want this
The output I'm looking for is:
textstring1
textstringtextrstring1
The part I can't figure out is how to deal with the variable length of the "good" text string.
Thanks
Best Answer
-
Is the literal string "textstring"?
I mean, you could run:
=IF(CONTAINS("textstring", String@row), "textstring")
This would just drop the string you're looking for into the cell if it contains your search phrase at all. If nothing else, this might be good for a helper column.
Answers
-
Are there any special characters that separate the appended text, and if not can you inject one? If so then you can look for that character and make the string length variable based on that character. For example, if you were able to inject a space your strings would look like:
textstring1 don't want this
textstringtextstring1 don't want this
And your formula would be *assuming the column name is String. Change this to your actual column name:
=LEFT(String@row, FIND(" ", String@row))
This formula takes the left most characters up to the number of digits to the first instance of your special character (here a space).
-
Thanks, @David Tutwiler
Unfortunately, there are not special characters, and I can't I can't inject anthing. The text I want is predictable only that it will share "textstring" but may be mixed in with other things. CONTAINS would normally work perfectly, but it seems to be getting thrown off by "don't want this" and not matching correctly. My thought was to remove the unwanted part of the string, leaving the good part, then running a CONTAINS.
-
Is the literal string "textstring"?
I mean, you could run:
=IF(CONTAINS("textstring", String@row), "textstring")
This would just drop the string you're looking for into the cell if it contains your search phrase at all. If nothing else, this might be good for a helper column.
-
Hmmm- you gave me an idea. There is more than 1 "good" text string, but there is a finite number. I could write a nested IF(CONTAINS( for each option with the appropriate output. I'm going to try that and see.
Thanks for the suggestion!
-
That did work! Thanks again.
-
Awesome! Glad it's working.
-
@David Tutwiler : I used this formula =LEFT(String@row, FIND(" ", String@row)) but it only works for those cells where the delimeter exists and I need it to work with all cells if finds a delimeter or not.
-
@Kevin7859 There has to be some sort of key that tells the formula where to stop. If it is a specific number of characters each time, then you don't need the find and you can just write =LEFT(String@row, 5) [5 is a random number that would have to be the same every time].
Otherwise, you might need to inject the special character so you can split the text. Otherwise, if there is no special character for a cut off and the text length of your target changes every time, there isn't a lot to be able to tell the formula how to programmatically split the string.
-
@David Tutwiler This is what I came up with and it works LEFT([School Name]@row, FIND(" (", [School Name]@row)) for the cells where the "(" is in the cell but is there isn't one it doesn't return the value. The reference cell is "school name (1234)" Trying to exclude everything after the name.
I have been trying to incorporate and IF function =IF(LEFT([School Name]@row, FIND(" (", [School Name]@row)), LEFT([School Name]@row, FIND(" (", [School Name]@row), [School Name]@row))))
But that keeps failing as well. Maybe making this more complicated than it needs to be.
-
Ah, ok. I think I understand. Let me make sure before I put out a suggested formula.
Assumption
I am assuming that you have a few different school types in your list. Some schools are listed with parentheses and numbers (ie. "School Name (1234)") and some are listed without (ie. "School Name"). You want only the school name for both scenarios, does that sound right?
Potential Solution
If the assumptions are correct, then you essentially just need to branch the formula with an IF statement to see if there is a parenthesis in the name or not. If there is not a parenthesis, just copy the name into the new cell. If there is a parenthesis then use the formula you have created. That formula would look like this:
Proposed Formula
=IF(FIND("(", [School Name]@row) = 0, [School Name]@row, LEFT([School Name]@row, FIND(" (", [School Name]@row)))
-
That was the correct assumption. Thank you for the assistance. Still trying to get used to the Smartsheet formulas compared to Excel.
-
No problem. It's a little different way of thinking, but once you get the hang of it I'm sure you'll be off to the races.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!