5 Replies Latest reply on Dec 10, 2008 6:27 PM by davidanders

# Extract First Name, Middle Initial and Last name from Full Name

### Title

Extract First Name, Middle Initial and Last name from Full Name

### Post

I am trying to deal with the fact that many times my company leases lists from third parties in which the first name, middle initial, and last name are contained in one field. I have tried to set up calcuation fields to split out first name, middle initial and last name, but I am not a practiced writer of calculations and have only been able to produce the most simple way of extracting the first name and last name. I have been unable to extract the middle initial. This is a more complicated task because a middle initial may or may not be included in the full name. Also, the calucations that I have employed for first name and last name do not take into account titles or suffixes.

Any help would be appreciated.

Here is the calucation I employed to extract the first name:

LeftWords ( Full Name ;1)

Here is the calucation that I employed to extract the last name:

RightWords ( Full Name ; 1 )

rowing190

• ###### 1. Re: Extract First Name, Middle Initial and Last name from Full Name

Hi rowing190

if you are SURE about your calculations, this will give you those middle initial :

TrimAll ( Substitute ( Substitute ( ¶ & Substitute ( FullName ; " " ; ¶ ) & ¶ ; [ ¶ & FirstName & ¶ ; ¶ ] ; [ ¶ & LastName & ¶ ; ¶ ] ) ; ¶ ; " " ) ; 1 ; 1 )

BTW: remember that there are double first names and double last names, too !

• ###### 2. Re: Extract First Name, Middle Initial and Last name from Full Name

Thank you so much for the formula. It worked, But is there some sort of if and then logic that I should be employing in case there is a title or suffix in the full name. My present calucations for do not take them into account. For example, if Jr. is present in the full name, my present calculation designates jr. as the last name. I can create an additional full name field from which to run this calculation and strip all suffixes from the name before I import the data. But I would rather not have to go through the additional work if I can write a calculation formula that will work.

Thanks again for the help.

rowing190

• ###### 3. Re: Extract First Name, Middle Initial and Last name from Full Name

Legend:

Title = T
FirstName = F
Initial = I
LastName = L
Suffix = S

No calculation can evaluate this:

T F F F I I L L L L S

There must be an uman intervent !

BTW: you can isolate the records ( and sure those are the most part ) that have 2 words into the field FullName, so that you can easily apply your formulas.
The omitted records must be evaluated by hand.

• ###### 4. Re: Extract First Name, Middle Initial and Last name from Full Name

Hi "

What you are needing to do is referred to as "text parsing".  You may want to take a look at an answer I provided to another forum user...

I hope this helps you and any other interested readers... Good Luck!!!

Kundinger