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!
I am guessing you want to remove everything before comma. Use below formula
REGEX_Replace([Field1], "(.*,\s)", "")
Workflow:
Hope this helps : )
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
Hello Maskell,
Thanks a lot for the solution!
What would change if I wanted to keep Smith instead of John? Thanks!
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?
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.
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?