Split a full name into first and last name in Excel
Split a full name into two columns with TEXTBEFORE and TEXTAFTER, or with LEFT, FIND and MID in older Excel. What to do with middle names.
The list has a title in row 1, so the first full name is in A3. In B3, type =TEXTBEFORE(A3," ") for the
first name. In C3, type =TEXTAFTER(A3," ",-1) for the last name.
Fill both down and every row splits. The -1 is the part people
leave out. It is also the part that makes middle names work.
| Row | A | B | C |
|---|---|---|---|
| 1 | Staff list | ||
| 2 | Full name | First | Last |
| 3 | Ada Lovelace | Ada | Lovelace |
| 4 | Ben Okri | Ben | Okri |
| 5 | Mary Ann Evans | Mary | Evans |
| 6 | Cai Nguyen | Cai | Nguyen |
How the two functions work
Both functions take a text and a delimiter. The delimiter is the character you split at. For a name, it is a space between two quotation marks. TEXTBEFORE returns the text before it, and TEXTAFTER returns the text after it.
The third argument is the instance. It says which space to use. 1 is the first space, and it is the default. 2 is the second. A minus number counts from the end, so -1 is the last space.
TEXTBEFORE and TEXTAFTER are in Microsoft 365, Excel 2024 and Excel for the web. They are not in Excel 2021 or earlier. For those versions, see the LEFT, FIND and MID section below.
Middle names: why the -1 matters
Row 5 holds Mary Ann Evans. It has 2 spaces, not 1. Watch what each formula returns.
=TEXTAFTER(A5," ") returns Ann Evans. It stops at the first
space, so the middle name comes too.
=TEXTAFTER(A5," ",-1) returns Evans. It stops at the last space.
On a two-word name, both versions give the same answer. So always type the -1. The first name has no such problem: TEXTBEFORE at the first space gives Mary.
Getting the middle name
Take everything after the first space. Then take everything before the last space of that result:
=IFNA(TEXTBEFORE(TEXTAFTER(A3," ")," ",-1),"")
For Mary Ann Evans it returns Ann. For John Paul George Smith it returns Paul George. For Ada Lovelace it returns an empty text.
The IFNA is there for two-word names. They have no second space, so TEXTBEFORE returns
#N/A. IFNA shows an empty text instead.
What no formula can know
A formula only sees spaces. It cannot tell that Mary Ann is one first name. It cannot tell that van der Berg is one surname. For Jan van der Berg, the -1 version returns Berg.
So split the whole column, then read the rows with 3 or more words. Fix those few by hand.
Rows that break the split
A space at the end
Some names arrive with a space after the last letter. You cannot see it. But now the last space is at the end, and nothing comes after it.
So =TEXTAFTER(A3," ",-1) returns an empty text. Two spaces
between the words cause a problem too. The first-space version then returns the last name with a
space in front.
Fix: clean the name first with TRIM.
=TEXTAFTER(TRIM(A3)," ",-1)
TRIM removes spaces at the start and the end. It also changes 2 spaces in a row into 1.
A name with no space
A one-word name, like Cher, has nothing to split at. TEXTBEFORE and TEXTAFTER both return
#N/A.
To show the whole name as the first name, type
=IFNA(TEXTBEFORE(A3," "),A3). It returns Cher.
Last name first, with a comma
Some lists hold names as Lovelace, Ada. Here the delimiter is the comma, not the space.
-
Last name:
=TEXTBEFORE(A3,",")returns Lovelace. -
First name:
=TEXTAFTER(A3,", ")returns Ada.
The second delimiter is a comma and a space together. With the comma alone, you get Ada with a space in front. The cell looks right, but its length is 4, not 3. A lookup on it returns #N/A.
Older Excel: LEFT, FIND and MID
Before TEXTBEFORE, you split a name in two steps. FIND gives the position of the space.
=FIND(" ",A3) returns 4 for Ada Lovelace. Then LEFT or MID takes
the text on each side.
First name: =LEFT(A3,FIND(" ",A3)-1) returns
Ada.
LEFT needs the number of characters to take. The space is at 4, so the name is 3 characters. That is the -1.
Last name: =MID(A3,FIND(" ",A3)+1,99) returns
Lovelace.
MID starts 1 character after the space. The 99 means "up to 99 characters". A longer number than you need gives you the rest of the text.
Leave out the -1 and LEFT takes the space too. The result is "Ada" with a space on the end. It looks right, but LEN on it returns 4, not 3.
The last word in older Excel
MID has the same middle-name problem. For Mary Ann Evans it returns Ann Evans. Older Excel has no -1, so you mark the last space yourself:
=RIGHT(A3,LEN(A3)-FIND("#",SUBSTITUTE(A3," ","#",LEN(A3)-LEN(SUBSTITUTE(A3," ","")))))
For Mary Ann Evans it returns Evans. Read it from the inside out:
-
LEN(A3)-LEN(SUBSTITUTE(A3," ",""))counts the spaces. For Mary Ann Evans, it is 2. -
SUBSTITUTE(A3," ","#",2)changes only the 2nd space to #. The text is now Mary Ann#Evans. - FIND gives the position of the #. RIGHT takes everything after it.
Any character works in place of #, if it is not in the names.
Without a formula
Flash Fill. Type the first name of row 3 by hand in B3. Select B4 and press Ctrl+E. Excel fills the column from your example. The result is plain text, not a formula. If a name changes later, the split does not change.
Text to Columns. Select the names. Then click Data → Text to Columns. Choose Delimited, then tick Space. Each word goes into its own column. So Mary Ann Evans takes 3 columns, and Evans lands one column to the right of Lovelace.
A formula is the better choice when the list will grow. A new row splits as soon as you fill the formula down.
Questions people ask
Why does TEXTBEFORE show #NAME?
Your Excel does not have the function. Usually that means Excel 2021 or earlier. Use the LEFT and FIND version instead.
What about TEXTSPLIT?
=TEXTSPLIT(A3," ") puts each word in its own cell, across the
row. It is also only in newer Excel. Like Text to Columns, a middle name moves the last name one
column to the right.
Where can I practise this?
The lessons below use these names on a real sheet. You type the formula and press Enter, and the answer is checked at once. To see how, read how your formula is checked.