Hi all,
I came across this problem and I've been wondering how to solve it. I have a table that looks a little bit like this:
FirstNameLastNameMiddleName(actually all 1 column)
John Doe Johnson
Mary Louise
Anthon Oparin Gilbert
I would like to separate these into a new column, at the last space delimiter, meaning it should look like this:
New Column:
Johnson
Louise
Gilbert
Is this possible? Thanks for your help.
Solved! Go to Solution.
Hi AkisM,
This can be done via the RegEx tool.
If the names are all separated by multiple spaces then you can use
\w+\s{2,}\w+\s{2,}(.*)
If the names are separated by a tab then you can use
\w+\t\w+\t(.*)
These expressions are placed in the RegEx tool configured as below.
Ah, I see - I thought from the example that there were multiple spaces or tabs.
To get the last word from each string use
^.*\s(.*)$
Configured as below
Thanks, that works fine! For the sake of the exercise, let's say we were looking for the last word, which was preceded by 1 or more spaces. How would we edit the expression to look for 1 or more spaces right before the last word?
Always a fan of challenges! 🙂
If the last word was preceded by 1 or more spaces, then the following RegEx will pull it out
^.*\s{2,}(.*)$
The curly brackets with a number and a comma indicate "Two or more spaces".
If you want to learn more about RegEx and how to make it work for you, then Alteryx Community has an excellent article to bring you up to speed.
Much appreciated. Last question I forgot to ask: We successfully separated the last word as a new column. How do I delete that last word from the original column? I don't want to see it in duplicate. I tried using Regex Replace with the expression you gave me but it just deletes everything instead 😛
No worries. You were right on the mark with Regex replace - but in this case we want to replace everything except what we wanted to parse out.
As such, the formula below will get the job done (for your challenge.xlsx data).
trim(REGEX_Replace([First Name], '^(.*)\s.*$', '$1'))
If there are 2 or more spaces we can edit it to become
trim(REGEX_Replace([First Name], '^(.*)\s{2,}.*$', '$1'))
Both of these get configured in the formula tool as below
Works like a charm, thank you!
@lmorrell Similar to this I need to pick it from left, excluding the last value.
for example
if value is this = 'IRH5 Comdty' then pick IRH5
And if value = 'L H5 Comdty' then pick L H5
Could you please suggest a solution here