Isolating all text to the right of a character (e.g. "") in a string
Hello Smart People 
I'm parsing a string using helper columns. I want to return all text to the right of my last "". For example, QAISOPS would return OPS, and QAARCRM would return RM.
Is there a simple way to do this?
Thank you!
Best Answer

First we need to find out how many there are.
=LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, "", "")
Now that we know how many we have, we can replace the last one with a unique character. For this you are going to want to choose something that is definitely NEVER going to be anywhere else within the string. In my example I will use CHAR(10) which is a line break.
=SUBSTITUTE([Column Name]@row, "", CHAR(10), LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, "", ""))
Now that we have a unique value as the last character to exclude, we can use a FIND to determine how many characters from the left that unique character is.
=FIND(CHAR(10), SUBSTITUTE([Column Name]@row, "", CHAR(10), LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, "", "")))
Subtracting that number from the total number of characters:
=LEN([Column Name]@row)  FIND(CHAR(10), SUBSTITUTE([Column Name]@row, "", CHAR(10), LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, "", "")))
Tells us how many characters to pull for the RIGHT function:
=RIGHT([Column Name]@row, LEN([Column Name]@row)  FIND(CHAR(10), SUBSTITUTE([Column Name]@row, "", CHAR(10), LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, "", ""))))
Plug that in and let me know how it goes.
It could also be simplified if we can work from certain assumptions such as it will always be after the 2nd "" and the first "" is always the 3rd character in the string. The above provides for the most amount of flexibility so that you can have has many hyphens as you want and the number of characters throughout doesn't matter.
Answers

First we need to find out how many there are.
=LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, "", "")
Now that we know how many we have, we can replace the last one with a unique character. For this you are going to want to choose something that is definitely NEVER going to be anywhere else within the string. In my example I will use CHAR(10) which is a line break.
=SUBSTITUTE([Column Name]@row, "", CHAR(10), LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, "", ""))
Now that we have a unique value as the last character to exclude, we can use a FIND to determine how many characters from the left that unique character is.
=FIND(CHAR(10), SUBSTITUTE([Column Name]@row, "", CHAR(10), LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, "", "")))
Subtracting that number from the total number of characters:
=LEN([Column Name]@row)  FIND(CHAR(10), SUBSTITUTE([Column Name]@row, "", CHAR(10), LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, "", "")))
Tells us how many characters to pull for the RIGHT function:
=RIGHT([Column Name]@row, LEN([Column Name]@row)  FIND(CHAR(10), SUBSTITUTE([Column Name]@row, "", CHAR(10), LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, "", ""))))
Plug that in and let me know how it goes.
It could also be simplified if we can work from certain assumptions such as it will always be after the 2nd "" and the first "" is always the 3rd character in the string. The above provides for the most amount of flexibility so that you can have has many hyphens as you want and the number of characters throughout doesn't matter.

Boom! Thanks Paul, very smart solution!!

Happy to help. 👍️

@Paul Newcome Could you help me write the formula that obscures all characters with * after first two characters? The idea is to partially mask the "lastname" ie: Smith: Sm***

@gwson The challenge with your ask is the variable number of letters that would need to be masked. If you wanted to mask a set number then it would be much easier. Otherwise you would end with a potentially very long formula that would be rather unwieldy, hard to troubleshoot, and restrictive.

@Paul Newcome thanks for your prompt reply. Would it help if we always want say, fifteen stars after the first two characters from the left? (the assumption is that the last name would never be longer than 15 chars) In other words, it doesn't really matter how many stars there are as long as all characters are masked after the first two from the left.

@gwson Yes. We can do that and you can have "unlimited" characters after the stars (assuming less than the built in limit of 4,000 characters per cell. So basically if you have 3 characters or 300 characters, we can set it up so that it is first and second character followed by any number of stars.
=LEFT([Column Name]@row, 2) + "**********"
The above would do 10 stars regardless of the number of characters so long as you have at least two characters. Just add more or less stars based on your needs.

@Paul Newcome wow, that's exactly what I needed, simple and elegant!

@gwson Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 202 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!