Extract the first, middle and last name in Microsoft Excel

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

CATEGORY: Excel Text , Excel Formula

VERSIONS: All Microsoft Excel Versions

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



1	2	3	4	5

RATING: 4.16
VIEWS: 45954

Email Tip

Print Tip

Add to My Tips

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

Sponsors:

Book Store:

VIEW ALL BOOKS

Recommended Books:

Related MS EXCEL TIPS:

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.