Create new column based on last space delimiter of existing column?

[AkisM]

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.

[lmorrell]

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.

Spoiler

[AkisM]

Hi and thanks for the response, unfortunately the RegEx above does not work.

[lmorrell · Accepted solution]

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

Spoiler

[AkisM]

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?

[lmorrell]

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. 

[AkisM]

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 😛

[lmorrell · Accepted solution]

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

Spoiler

[AkisM]

Works like a charm, thank you!

[Austin004lasrado]

@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