Excel guide · 3 min read

How to separate first and last name in Excel

Q: How do I separate first and last name with a formula?

In Excel 365 use =TEXTBEFORE(A2," ") and =TEXTAFTER(A2," "). In older versions use =LEFT(A2,FIND(" ",A2)-1) and =MID(A2,FIND(" ",A2)+1,100).

Q: How do I split "Last, First" names?

Split at the comma and space instead: =TEXTAFTER(A2,", ") gives the first name and =TEXTBEFORE(A2,",") gives the last name. In Text to Columns, tick Comma and Space.

Q: Does TEXTSPLIT work for names?

Yes, in Excel 365 =TEXTSPLIT(A2," ") spills each word into its own column. It is handy when names have different numbers of parts, but you lose control over which part counts as the last name.

Quick answerSelect the name column, go to Data > Text to Columns, choose Delimited, tick Space, and click Finish. Excel puts the first name in one column and the last name in the next. To keep it live with a formula, use =TEXTBEFORE(A2," ") for the first name and =TEXTAFTER(A2," ") for the last name in Excel 365.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B
1	Full name	Email
2	Grace Hopper	grace@navy.org
3	Alan Turing	alan@bletchley.uk
4	Ada Lovelace	ada@engine.co
5	Katherine Johnson	kj@nasa.gov
6	Tim Berners-Lee	tim@w3.org
7	Mary Ann Evans	mae@press.com

Method 1: Text to Columns (any version)

Text to Columns splits every cell at the character you choose. It is quick and works everywhere, but the result is static and it overwrites whatever is in the columns to the right. Insert a couple of empty columns first.

A name with three parts, like Mary Ann Evans, splits into three columns. You will need to tidy those by hand or use a formula instead.

Insert two empty columns to the right of the names.
Select the names (A2:A7).
Go to Data > Data Tools > Text to Columns.
Choose Delimited and click Next.
Tick Space, untick the others, and tick "Treat consecutive delimiters as one".
Click Finish.

Method 2: Flash Fill

Type the first name for the first row, and Flash Fill (Excel 2013 and later) works out the pattern for the rest. Do the same in another column for last names. It is fast and handles hyphens well, but check the odd cases.

In C2, type Grace.
Select C3 and press Ctrl+E, or go to Data > Flash Fill.
In D2, type Hopper, select D3, and press Ctrl+E again.
Check the rows with three-part names, since Flash Fill has to guess.

Method 3: TEXTBEFORE and TEXTAFTER (Excel 365)

These two functions are made for this job. TEXTBEFORE returns everything before the first space and TEXTAFTER returns everything after it. Because they are formulas, the split updates if a name changes.

=TEXTBEFORE(A5," ")

Result: Katherine

Returns "Katherine".

Get the last name with TEXTAFTER

TEXTAFTER splits at the first space by default, so "Mary Ann Evans" gives "Ann Evans". To get only the final word, ask for the last space with -1 as the instance number: =TEXTAFTER(A7," ",-1) returns "Evans" in Excel.

=TEXTAFTER(A6," ")

Result: Berners-Lee

Returns "Berners-Lee". The hyphen is not a space, so the double-barrelled surname stays together.

Method 4: LEFT, MID and FIND (older Excel)

If TEXTBEFORE gives #NAME?, your Excel is older. FIND locates the space, and LEFT takes everything before it. For the last name, MID starts one character after the space and takes the rest.

For the last name use =MID(A2,FIND(" ",A2)+1,100). The 100 just means "plenty of characters".

=LEFT(A2,FIND(" ",A2)-1)

Result: Grace

Returns "Grace". FIND gives 6 for the space, so LEFT takes the first 5 characters.

Common mistakes

Text to Columns overwrites your data

It writes into the columns to the right without asking. Insert empty columns first, or set a different Destination in the last step.

Leading or double spaces break the split

A name like " Grace Hopper" splits at the wrong space. Clean it with TRIM first, or wrap the reference: =TEXTBEFORE(TRIM(A2)," ").

Middle names land in the last-name column

Decide on a rule. Use TEXTAFTER with -1 to keep only the final word as the surname, and check compound surnames like "van der Berg" by hand.

#VALUE! from FIND

The cell has no space, often a single name. Wrap it: =IFERROR(LEFT(A2,FIND(" ",A2)-1),A2).

FAQ

How do I separate first and last name with a formula?

In Excel 365 use =TEXTBEFORE(A2," ") and =TEXTAFTER(A2," "). In older versions use =LEFT(A2,FIND(" ",A2)-1) and =MID(A2,FIND(" ",A2)+1,100).

How do I split "Last, First" names?

Split at the comma and space instead: =TEXTAFTER(A2,", ") gives the first name and =TEXTBEFORE(A2,",") gives the last name. In Text to Columns, tick Comma and Space.

Does TEXTSPLIT work for names?

Yes, in Excel 365 =TEXTSPLIT(A2," ") spills each word into its own column. It is handy when names have different numbers of parts, but you lose control over which part counts as the last name.

Why does Flash Fill get some names wrong?

It guesses from your examples. Give it one or two more examples in tricky rows, such as a three-part name, and it usually corrects the rest.

Functions used

Related guides

Updated October 31, 2026