Google Exceltip.com
Account Icon
Shopping Cart
CheckOut

» Extract the first, middle and last name in Microsoft Excel




Enter the following name in cell A1: George W. Bush

Use the following function to extract the first name:
=LEFT(A1,FIND(" ",A1)-1)

The Result: George

Use the following function to extract the middle name:
=IF(ISERR(MID(A1,FIND(" ",A1)+1,IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)), FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1))-FIND(" ",A1)-1)),"",MID(A1,FIND(" ",A1)+ 1,IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)),FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1))-FIND(" ",A1)-1))

The result: W.

Use the following function to extract the last name:
=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

The result: Bush

Rate this tip
12 34 5
  RATING: 4.16
  VIEWS: 45954
Cool
Don Cherry wrote on December 31, 1969 18:00 EST
The only thing missing is for the rare case when there are two words in someone's last name, e.g. "Art X Van Delay" the last name is "Van Delay" but you only wind up with "Delay."
Lucky Roulette
John wrote on December 31, 1969 18:00 EST
Greetings to all! Excuse for this message, but at you excellent design of a site! Very much it was pleasant to me, I shall come here very often!
How to combine?
KY wrote on December 31, 1969 18:00 EST
After the words are separated, how do you combine Column A "George" with Column B "Bush" and come up with Column C "Bush, George"?

Thanks, much!
K, from Manila
saved me
Mike wrote on December 31, 1969 18:00 EST
I had a time sensitive document that I had to use this function and this saved me.

Thanks a ton



REGISTERED USERS click here to post comments


GUESTSclick here to Register
Name
Comment Title
Comments


Terms and Conditions of use
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Copyright © 2003 ExcelTip.com
Microsoft, Microsoft Excel is a U.S. registered trademark of Microsoft Corporation