community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

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

Asteroid

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.

Alteryx Certified Partner

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
Asteroid

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

Alteryx Certified Partner

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
Asteroid

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?

Alteryx Certified Partner

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. 

Asteroid

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

Alteryx Certified Partner

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
Asteroid

Works like a charm, thank you!

Labels