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

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