Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Extract word(s) after \

    Community Champion
    in Formulas and Functions

    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)

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answers

    • ✭✭✭✭✭✭
      Answer ✓

      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:

      image.png


      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"

      · Share on FacebookShare on Twitter
    • Community Champion
      Answer ✓

      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?

      · Share on FacebookShare on Twitter
    • Community Champion
      Answer ✓

      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

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭✭
      Answer ✓

      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:

      image.png


      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"

      · Share on FacebookShare on Twitter
    • Community Champion

      @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.
      · Share on FacebookShare on Twitter
    • Community Champion
      Answer ✓

      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?

      · Share on FacebookShare on Twitter
    • Community Champion

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

      · Share on FacebookShare on Twitter
    • Community Champion

      @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

      image.png

      Is the Factory portion not always last?

      Kelly

      · Share on FacebookShare on Twitter
    • Community Champion

      @Kelly Moore see below:

      image.png


      · Share on FacebookShare on Twitter
    • Community Champion
      Answer ✓

      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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭
      edited 10/18/21

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

      · Share on FacebookShare on Twitter
    • Community Champion

      Thanks @Kelly Moore

      · Share on FacebookShare on Twitter
    • ✭✭

      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?

      image.png


      image.png




      image.png


      · Share on FacebookShare on Twitter
    • Community Champion

      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

      · Share on FacebookShare on Twitter
    • Community Champion

      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

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions