Formula Assistance - Pulling values after delimiter in text string

ConnorForm

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!!

Neil Egsgard

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

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

Neil Egsgard

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

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

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.

Andy Y

@Neil Egsgard that worked for me, too, thank you!