We're excited to announce that we'll be partnering with Credly starting October 19th - see what this means and read the announcement blog here!

Alteryx Designer Discussions

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

Parse Name fields

aparna0208
8 - Asteroid

Hi,

 

I am using below regex to split full name into first and last name. 

(^\w+) (\w+)

 

The output is as below 

 

Full Name                                      First                    Last

Bob Williams JR                            Bob                     Williams

Gary Abott SR                               Gary                     Abott

Bob Alexander II                            Bob                      Alexander

 

In all the above cases, the suffix such as JR,SR,II etc is missing but it needs to appended with last name as Willliam JR, Abott SR etc. My formula almost works except for the suffix part. I want that in the last name column when it splits.

 

Also, when I try (^\w+)\s(\w+ .*), I get results for the one with suffix as expected but other rows are NULL

 

Full Name                                      First                    Last

Bob Williams JR                            Bob                     Williams

Gary Abott                                     NULL                    NULL

Bob Alexander II                            Bob                      Alexander

Robert Scott                                   NULL                   NULL

 

Need some help on the formula to split the columns into two(first and last name) and append the suffix with last name

 

I am expecting an output as below

 

Full Name                                      First                    Last

Bob Williams JR                            Bob                     Williams JR

Gary Abott SR                               Gary                     Abott SR

Bob Alexander II                            Bob                      Alexander II

 

Thank you in advance!

 

 

6 REPLIES 6
fmvizcaino
16 - Nebula
16 - Nebula

Hi @aparna0208 ,

 

Try the following regular expression and let me know if that works for you.

(^\w+) ([\w\s]+)

 

Best,

Fernando V.

JoshuaGostick
11 - Bolide

Hi @aparna0208,

 

If there are no middle names, you could something simple like:

 

(^\w+) (.+)

 

This will take the first name and leave everything else in the last name column. Does this work for you?

 

Thanks,

Josh

aparna0208
8 - Asteroid

@JoshuaGostick @fmvizcaino Awesome! both worked. thank you so much for your quick response:)

bpatel
Alteryx
Alteryx

hi @aparna0208 ,

 

you could try the text to column tool or (\<\w+\>)(.+) in the regex tool. i attached a workflow. hope this helps!

aparna0208
8 - Asteroid

This worked too! thank you:) @bpatel 🙂

fmvizcaino
16 - Nebula
16 - Nebula

Not one but three answers in a matter of seconds!!

That's great @bpatel , @JoshuaGostick , @aparna0208 !!!

Labels