Extract word(s) after \

Neil Watson

Hi, I have a long text string and need to extract the word (or words) after the rightmost "separator", which is a " \ " (space forward slash space).

It's in the format text \ text \ text

The number of words is not consistent in the text string (hope this makes sense)

Bassam Khalil

Hi @Neil Watson 

Hope you are fine, please try the following formula:

=MID([Column16]@row, (1 + FIND("/", [Column16]@row)), 1000) 

the following screenshot shows the result:

Kelly Moore

Hey @Neil Watson

Building on @Bassam Khalil 's approach, I used the ANSCII/HTML Character set to represent the "\" character. Special characters have unique codes to represent them. The forward slash is designated as CHAR(92). You can find a complete reference here

Since it appears your Factory is last, I used the Right function. If the Factory is not at the end, see Bassam's formula above.

=RIGHT([your text column]@row, FIND(CHAR(92), [your text column]@row) - 1)

Will this work for you?

Kelly Moore

Hey @Neil Watson

This formula worked in my test sheet as a work around. Does it work for you?

=IFERROR(RIGHT([text column]@row, FIND(CHAR(62), [text column]@row) - 1), RIGHT([text column]@row, FIND(CHAR(92), [text column]@row) - 1))

Kelly

Bassam Khalil

Hi @Neil Watson 

Hope you are fine, please try the following formula:

=MID([Column16]@row, (1 + FIND("/", [Column16]@row)), 1000) 

the following screenshot shows the result:

Neil Watson

@Bassam Khalil thanks for the suggestion. I have run into 2 issues:

1. The text string is in the format Company \ Division \ Area \ Factory. I want to extract the factory. The formula above extracts everything after the first \ since it works from the left.
2. Of more concern is the fact that for some reason Smartsheet has an issue with the "\". I can enter "\" in the formula, and it disappears for some reason. I am not sure if this is a bug or something else I am not understanding. "/" works fine....its just "\" that is an issue.

Kelly Moore

Hey @Neil Watson

Building on @Bassam Khalil 's approach, I used the ANSCII/HTML Character set to represent the "\" character. Special characters have unique codes to represent them. The forward slash is designated as CHAR(92). You can find a complete reference here

Since it appears your Factory is last, I used the Right function. If the Factory is not at the end, see Bassam's formula above.

=RIGHT([your text column]@row, FIND(CHAR(92), [your text column]@row) - 1)

Will this work for you?

Neil Watson

@Kelly Moore @Bassam Khalil thanks for your ideas. I now have a formula sort of working. The problem is that as data has come through the text string that I need to extract from varies in length i.e.

Company \ Division \ Area \ Factory

Company \ Division \ Area \ Line \ Factory

The way I see it, the formula needs to search from the right, which is not possible.

Kelly Moore

@Neil Watson

Sorry, I'm not following- is my formula not working?. The formula I provided will look for the last Forward-Slash in the string then return text. The length of the string to the left does not impact my formula- as long as the Factory portion is the last segment in the string

Is the Factory portion not always last?

Kelly

Neil Watson

@Kelly Moore see below:

Kelly Moore

Hey @Neil Watson

This formula worked in my test sheet as a work around. Does it work for you?

=IFERROR(RIGHT([text column]@row, FIND(CHAR(62), [text column]@row) - 1), RIGHT([text column]@row, FIND(CHAR(92), [text column]@row) - 1))

Kelly

Bassam Khalil

@Kelly Moore

Great formula for extracting text. it must be in the best practice.👍️

Neil Watson

Thanks @Kelly Moore

Marty Clouser

Hi @Kelly Moore, I applied your formula to a text string that I just want to extract the last word which is varied in length. Every time the formula extracts six characters which works fine for Kaiser. But if the last word is longer it only extracts the last six characters. i.e. "rtinez" for "Martinez". How do I extract the last word regardless of length?

Kelly Moore

Hey @Marty Clouser

Sorry for the delay, I just found your email in my inbox. Let me test this and I'll get back to you tonight

Kelly Moore

Hey @Marty Clouser

Try this. This takes advantage of the SUBSTITUTE function's ability to designate what occurrence, if multiple occurrences of the same search term is found, to substitute. The formula below points to the 3rd occurrence of your ">" and specifically finds that. By finding the length of the entire string, the formula takes into account string length

=RIGHT([Lat-EstateOf]@row, LEN([Lat-EstateOf]@row) - FIND("~", SUBSTITUTE([Lat-EstateOf]@row, CHAR(62), "~", 3)))

Will this work for you?

Kelly