Isolating all text to the right of a character (e.g. "-") in a string

Mark Hibyan

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, QA-IS-OPS would return OPS, and QA-ARC-RM would return RM.

Is there a simple way to do this?

Thank you!

Paul Newcome

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.

Paul Newcome

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.

Mark Hibyan

Boom! Thanks Paul, very smart solution!!

Paul Newcome

Happy to help. 👍️

gwson

@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***

Paul Newcome

@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.

gwson

@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.

Paul Newcome

@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.

gwson

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

Paul Newcome

@gwson Happy to help. 👍️