How do you split a name to get first name and last name?

Question

I have a column which contains a list of names, I want two other columns to contain functions which extract the first and last name. So far I have this

FirstName: =LEFT(D3,FIND(" ",D3))
LastName: =RIGHT(D3,LEN(D3)-FIND(" ",D3))

This works for names in the format "First Last", but it doesn't work when there is extra information such as "Mr. First Last".

Is there a better way to go about this?

Answer 1

There's no foolproof way to do it, even ignoring titles and suffixes and stuff. Consider the following two names:

Edward Van Halen
David Lee Roth

The last names are "Van Halen" and "Roth", but there's no algorithmic way to tell the difference.

Answer 2

Probably best for StackOverflow, but there is no easy way in general. You can have a list of allowable prefixes and suffixes to make your algorithm better. But consider ...

Dr. Jack Johnson Smith, PhD
Mr. Jim S. Van De Berg, Jr.

... splitting on just spaces is never going to get it completely right.

Answer 3

Also try to think about different cultures.

Just one example from Dutch: full name "Johannes Ernestus Maria van den Brink" splits up into first name "Johannes", middle names "Ernestus Maria", last name "van den Brink" (which should sort under B!).

Best solution (as in only 100% working) is to have separate name fields and an import method that lets the user enter the right pieces in the right fields.

So... good luck...

Answer 4

And it also fails for names with more first names or more last names. You really should store them separately. You should also split your input form into specific parts, like title, first name, last name. This way you can handle the possible spaces correctly.

You should extend your sheet with "first name" etc. columns, and try to convert automatically as much names as possible, then examine the results and apply corrections as needed, by hand. After this work your data will be much more easy to use and extend.

Answer 5

You can try to replace the values of (Mr. Mrs. Dr. Ms. Miss etc) with nothing and try to split the names that way. Try a test to see how many spaces are in the cell to see if there is more than just 'First Last'. If so, replace the values above with nothing. If you still have more than 1 space in it, then check for commas and remove everything from the comma to the end. It should help clean up the cells.

-JFV

Answer 6

Another option is to split up the name column into logical parts.

Say something like Salutation, First name, and Last name. That way you can build a name from the three columns, optionally working with the Salutation. If you wanted, throw in a Middle initial as well.

In this particular case, it's easier to build from parts than it is to try and deconstruct.

Answer 7

There's no foolproof way to do it, even ignoring titles and suffixes and stuff. Consider the following two names:

Edward Van Halen David Lee Roth

The last names are "Van Halen" and "Roth", but there's no algorithmic way to tell the difference.

I found a way to tell the difference between these though not the prefix/suffix part. Assuming the full name is in B2 and you only want the last name the formula is

=IF(NOT(ISERROR(FIND(" ",B2,FIND(" ",B2)+1))),RIGHT(B2,LEN(B2)-FIND(" ",B2,FIND(" ",B2)+1)),RIGHT(B2,LEN(B2)-FIND(" ",B2)))

Answer 8

Microsoft Outlook has a pretty good algorithm for this. You can automate Outlook, but there is a definite performance hit.

http://www.dailydoseofexcel.com/archives/2004/11/11/parse-names-with-outlook/