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!!
Best Answers
-
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
- SUBSTITUTE creates a unique character in place of the third "_". Adjust the number "3" in the substitute to be "14" for your application.
- FIND identifies the position of that unique character in the string.
- LEN provides the length of the entire string.
- LEN - FIND provides the number of characters for the RIGHT function.
- RIGHT provides the characters at the end of the string.
Regards,
Neil Egsgard
-
Thank you so much @Neil Egsgard that worked!!!
Answers
-
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
- SUBSTITUTE creates a unique character in place of the third "_". Adjust the number "3" in the substitute to be "14" for your application.
- FIND identifies the position of that unique character in the string.
- LEN provides the length of the entire string.
- LEN - FIND provides the number of characters for the RIGHT function.
- RIGHT provides the characters at the end of the string.
Regards,
Neil Egsgard
-
Thank you so much @Neil Egsgard that worked!!!
-
@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.
-
@Neil Egsgard that worked for me, too, thank you!
Help Article Resources
Categories
Check out the Formula Handbook template!
