How to Split a Full Name into First and Last Name in Excel

Separate first and last names from a single "Full name" column with formulas — including names that have a middle name.

You will learn to

• Extract the first name before the first space
• Extract the last name after the space
• Handle middle names by splitting at the last space

How it works

For a name like "Maria Gonzalez" the first name is everything before the first space and the last name everything after it. In Excel 365: =TEXTBEFORE(A2," ") and =TEXTAFTER(A2," "). In older versions: =LEFT(A2,FIND(" ",A2)-1) and =RIGHT(A2,LEN(A2)-FIND(" ",A2)).

Middle names break the simple version — "Mary Ann Lee" would give a last name of "Ann Lee". Split at the last space instead: =TEXTAFTER(A2," ",-1) returns "Lee". Run TRIM over the column first if the data might contain double spaces, and spot-check compound surnames such as "De la Cruz", which no formula can guess.

=TEXTBEFORE(A2," ")