|

» 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
 1 2 3 4 5
Rating: 4.02     Views: 155601
Lance  Posted on: 31-12-1969
How does one separate out JR's?

eg. John W. Smith Jr.
Roger  Posted on: 31-12-1969
Try this to find 'Smith Jr.'
=IF(ISERROR(SEARCH(" ",A2,SEARCH(" ",A2)+1)),RIGHT(A2,LEN(A2)-SEARCH(" ",A2)),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2)+1)))
Name
Comment Title