Excel Formulas

Extract the first, middle and last name in Microsoft Excel

 

Enter the following name in cell A1: Peter Bendor Samuel 

 

Extract the first, middle and last name in Microsoft Excel

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

The Result: Peter

 

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: Bendor

 

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

The Result: Samuel

 

Combine cells with first name and last name into email address?

if the names are in A1 and B1, use: =A1 & "." & B1 & "@somewhere.com"