Removing everything before/after a particular character

[veronikababiciute]

How can I remove everything before a particular character (including that character)? So, I have 'Johnson, John' and want to have only 'John'?

Thank you! 

[atcodedog05]

Hi @veronikababiciute 

I am guessing you want to remove everything before comma. Use below formula

REGEX_Replace([Field1], "(.*,\s)", "")

Workflow:

Hope this helps : )

[messi007]

@veronikababiciute,

You can use RegEx

Attached the workflow.

Hope this helps!

Regards

[Maskell_Rascal]

Hey @veronikababiciute,

Both of the RegEx solutions provided will work, and I'm a big advocate for using it when I can. But just incase you aren't well versed in RegEx, let me provide an alternative solution that looks a little more like a traditional Excel formula. 

Substring([Field1],FindString([Field1], " ")+1,Length([Field1]))

This formula is similar to the MID function in Excel. I'm using a FindString function to get the position of the first space, I then add one to it to specify my starting position, and finally the length is just the remaining characters in that field.  

Cheers!

Phil

[veronikababiciute]

Hello Maskell, 

Thanks a lot for the solution! 

What would change if I wanted to keep Smith instead of John? Thanks!

[atcodedog05]

Hi @veronikababiciute 

Can you provide more clarity what do you mean keep Smith from John Smith?

Would the input name be John, Smith (current scenario as above input with comma inbetween) or John Smith?

[Maskell_Rascal]

Also, for clarity, since it wasn't in the example I provided. The name I was parsing was "Adams, John Smith". So it is technically doing what you asked. I was merely showing an example of how it will search for the first space it can find and leave the rest. So if you had someone with the first name of Billy Bob, it will still maintain their full name. 

[Rob48]

I have the same issue with my data however I keep running into the problem of  certain special characters invalidating the RegEx or other formula.  For example my data will come with an asterisk or parenthesis that I need to ID and remove all characters before/after, however these characters alter the logic of the formula as in:

Original data	Processing Goal
(21) Items	Remove all characters before the second parenthesis, including the parentheses
31 * items	remove all characters before the asterisk, including the asterisk

Is there a solution that doesn't involve converting the offending special character to one that won't alter the formula?

[JPSeagull]

@Rob48 - I have the same issue. Looks like you might have to repost this question as a new topic.

[Rob48]

I've developed workarounds by using some specialized formulae in the Multi-Field tool rather than RegEx.  If you can provide a specific example of your issue I may be able to assist you.