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

Parsing Data - First Name, Middle Initial, Last Name

aburkhart
5 - Atom
I need assistance with how to split a cell into separate columns for first name, middle initial, and last name. However, not all the records in the source data contain a middle initial. I previously have used the RegEx function ([a-z]+)\s([a-z]+) to get first name / last name, but do not know how to modify the expression for the sometimes occurring middle initial. Do you have any recommendations?
3 REPLIES 3
NicoleJohnson
ACE Emeritus
ACE Emeritus
Try this one:

(^[[:alpha:]]+)\s*([[:alpha:]]*)\s+([[:alpha:]]+$)


1. ^ for start of field
2. [[:alpha:]] to capture all letters a-z & A-Z
3. \s*([[:alpha:]]*)\s+ ... the * for the first space and the middle name allows it to look for "0 or more" instead of "1 or more" with the + sign
4. $ for the end of the field (if you don't have this, it will put everything except the last letter of the last name in the middle name column)

Hope that helps! (Note: This might still give you trouble if you have a first name or a last name that contains a space, such as "Lou Ann" or "Joe Bob"... something to watch out for.)

NJ
aburkhart
5 - Atom

Thank you! That worked for my data

SBA
5 - Atom

Thanks for this solution - it works for most of my data except for cases where there are multiple middle names. How would you suggest that I fix this? There can be multiple middle names, so my logic is to take the first word as first name, last word as middle name, and everything else as middle names (in a single field).

 

Thanks

Sam

Labels