Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

AkisM
10 - Fireball

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.

8 REPLIES 8
lmorrell
11 - Bolide

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
Regex config.png
AkisM
10 - Fireball

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

lmorrell
11 - Bolide

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
Regex config.png
AkisM
10 - Fireball

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
11 - Bolide

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
10 - Fireball

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
11 - Bolide

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
Regex replace.png
AkisM
10 - Fireball

Works like a charm, thank you!

Labels