Extract word(s) after \
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)
Best Answers

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:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

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?

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
Answers

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:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

@Bassam Khalil thanks for the suggestion. I have run into 2 issues:
 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.
 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.

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

Sorry, I'm not following is my formula not working?. The formula I provided will look for the last ForwardSlash 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

@Kelly Moore see below:

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

Great formula for extracting text. it must be in the best practice.👍️
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Thanks @Kelly Moore

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?

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

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([LatEstateOf]@row, LEN([LatEstateOf]@row)  FIND("~", SUBSTITUTE([LatEstateOf]@row, CHAR(62), "~", 3)))
Will this work for you?
Kelly
Help Article Resources
Categories
Check out the Formula Handbook template!