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
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!