Parsing Data - First Name, Middle Initial, Last Name

[aburkhart]

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?

[NicoleJohnson · Accepted solution]

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]

Thank you! That worked for my data

[SBA]

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