Parse Last Name using Regex

[spro]

I'm trying to split one column containing Last name and suffix into two different columns. The issue I'm experiencing is multiple Last names separated by a space.

I want Last names in one column and suffix if any in a second column. I used regex to create a column with just the suffix but I don't know how to remove it from the Last name column.

This is an example of my data with multiple Last names in a single column:
Last Name
Gonzalas Soto Rojo Perez JR.
Morales Garcia Jr.
Navarro Valencia
Platt
Rhodes II
Sosa Sparks Phd
Stewart

Desired output, 2 columns:

Lastname	Suffix
Gonzalas Soto Rojo Perez	JR.
Morales Garcia	Jr
Navarro Valencia
Platt
Rhodes	II
Sosa Sparks	Phd
Stewart

Any suggestions are much appreciated!

-shirley

[fmvizcaino · Accepted solution]

Hi @spro ,

I'm attaching a regex based solution. My idea was to identify any 3 letter word and separate as a suffix

Best,

Fernando Vizcaino

[MarqueeCrew]

Did you know that you can:

countwords([name])

it could be used to find the last word.  

if countwords([name]) > 2

getword(countwords([name]-1)

else null

endif

you can build a list of suffixes in a text input tool and use a find replace tool to Check for matches (case insensitive). 

just some thoughts to help. 

cheers,

 mark

[spro]

Awesomeness, this works!

Thank you so much.

-shirley