Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
If you can separate them, then go with @bandi's solution. If you can't, try replacing known values you don't want with nothing (""). –  JFV Jul 21 '09 at 0:11
4  
This is a constant problem with name data, unfortunately. The only real solution is either doing it computationally and then correcting the mistakes manually or just doing it manually all the way. Since the only one who really knows what is part of the first name and the last name in all cases is the one who types it in, one should always take care to have a first name and last name field for the user. –  Stefan Thyberg Jul 21 '09 at 0:25
1  
It would be helpful if "Excel" were in the title of this question. –  jtbandes Jul 21 '09 at 3:28
    
Some related questions on StackOverflow: is.gd/1G7zH is.gd/1G7Bg is.gd/1G7Cr is.gd/1G7D0 is.gd/1G7Dx (Good luck!) –  peSHIr Jul 21 '09 at 7:40
add comment

9 Answers

up vote 7 down vote accepted

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.

share|improve this answer
4  
"Falsehoods Programmers Believe About Names": kalzumeus.com/2010/06/17/… –  coneslayer Jul 28 '10 at 19:15
add comment

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.

share|improve this answer
add comment

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...

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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/

share|improve this answer
add comment

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)))
share|improve this answer
add comment

I had the same problem. No matter how many names you have for a person you can have them in separate fields very easily. I copied all the names from excel to notepad. Then I imported that text file into excel and while importing i selected the option delimited and then i selected the space delimited.

In order to have better results you can clean data by removing initials e.g faisal s. tahir change it to faisal tahir.

Now when you import the text file into excel as space delimited then it will automatically put all names (first, second, third, fourth, fifth) into different columns.

share|improve this answer
1  
This is a good idea, however it fails if a name has multiple parts. To borrow from an earlier answer: "Johannes Ernestus Maria van den Brink" should split into " "Johannes" "Ernestus" "Maria" "van den Brink". (or even "Brink, van den"). Your method would mangle the last. –  Hennes Dec 18 '12 at 13:42
    
Well its impossible to write a code or formulae when you do not have a standard way of writing names. If you are breaking a name by way of putting a space between names then its not possible to tell the computer to treat spaces in the name mentioned by you as one name and in other cases treat it as separate names. It worked for me. Moreover there would be very few names where one name is written separately, so once you have applied the formulae then you can deal with such names manually. –  Faisal Dec 20 '12 at 11:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.