Extract word(s) after \

Options

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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓
    Options

    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:


    PMP Certified

    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"

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓
    Options

    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:


    PMP Certified

    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"

  • Neil Watson
    Neil Watson ✭✭✭✭✭✭
    Options

    @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
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Neil Watson ✭✭✭✭✭✭
    Options

    @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
    Kelly Moore ✭✭✭✭✭✭
    Options

    @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
    Neil Watson ✭✭✭✭✭✭
    Options
  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Bassam Khalil ✭✭✭✭✭✭
    edited 10/18/21
    Options

    @Kelly Moore

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

    PMP Certified

    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"

  • Neil Watson
    Neil Watson ✭✭✭✭✭✭
    Options
  • Marty Clouser
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!