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”