Formula Assistance - Pulling values after delimiter in text string

Hello all,

Trying to make a formula that pulls the 11-15 digits following the 14th "_" in the "Ad Name" Column. My formula is currently =RIGHT([Ad Name]@row, 12) however the length of the text I need can range from 11-15. Is there a formula that pulls everything after the 14th "_" that doesn't specify how many characters to grab (Since my situation it varies/not a set number)


Thanks for the time and assistance!!



Tags:

Best Answers

  • Neil Egsgard
    Neil Egsgard ✭✭✭✭✭
    edited 01/25/24 Answer ✓

    Hi @ConnorForm ,

    You use a combination of RIGHT(), LEN(), FIND(), and SUBSTITUTE(). Here is an example of a formula that works.

    Formula in "Last characters" field

    =RIGHT([Primary Column]@row, LEN([Primary Column]@row) - FIND("~", SUBSTITUTE([Primary Column]@row, "_", "~", 3)))


    Results


    Explanation

    1. SUBSTITUTE creates a unique character in place of the third "_". Adjust the number "3" in the substitute to be "14" for your application.
    2. FIND identifies the position of that unique character in the string.
    3. LEN provides the length of the entire string.
    4. LEN - FIND provides the number of characters for the RIGHT function.
    5. RIGHT provides the characters at the end of the string.

    Regards,

    Neil Egsgard

  • ConnorForm
    ConnorForm ✭✭✭✭
    Answer ✓

    Thank you so much @Neil Egsgard that worked!!!

Answers

  • Neil Egsgard
    Neil Egsgard ✭✭✭✭✭
    edited 01/25/24 Answer ✓

    Hi @ConnorForm ,

    You use a combination of RIGHT(), LEN(), FIND(), and SUBSTITUTE(). Here is an example of a formula that works.

    Formula in "Last characters" field

    =RIGHT([Primary Column]@row, LEN([Primary Column]@row) - FIND("~", SUBSTITUTE([Primary Column]@row, "_", "~", 3)))


    Results


    Explanation

    1. SUBSTITUTE creates a unique character in place of the third "_". Adjust the number "3" in the substitute to be "14" for your application.
    2. FIND identifies the position of that unique character in the string.
    3. LEN provides the length of the entire string.
    4. LEN - FIND provides the number of characters for the RIGHT function.
    5. RIGHT provides the characters at the end of the string.

    Regards,

    Neil Egsgard

  • ConnorForm
    ConnorForm ✭✭✭✭
    Answer ✓

    Thank you so much @Neil Egsgard that worked!!!

  • Neil Egsgard
    Neil Egsgard ✭✭✭✭✭

    @ConnorForm , wonderful! Glad to pass on help. I have received good support from the Smartsheet Community too. Could you mark my answer as the answer so it appears at the top when other people are referencing this type question, please? It makes the solution easier to find.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!