Extract a Text String to remove unwanted text

AFlint

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

David Tutwiler

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.

David Tutwiler

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).

AFlint

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.

David Tutwiler

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.

AFlint

@David Tutwiler -

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!

AFlint

@David Tutwiler

That did work! Thanks again.

David Tutwiler

Awesome! Glad it's working.

Kevin7859

@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.

David Tutwiler

@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.

Kevin7859

@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.

David Tutwiler

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)))

Kevin7859

https://community.smartsheet.com/discussion/comment/398689#Comment_398689

That was the correct assumption. Thank you for the assistance. Still trying to get used to the Smartsheet formulas compared to Excel.

David Tutwiler

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.