How to / Text
How to split first and last name in Excel
For the first name, use =TEXTBEFORE(A2, " "); for the last name, =TEXTAFTER(A2, " "). Both split at the first space.
Everything before the first space
| A | B | C | |
|---|---|---|---|
| 1 | Full name | ||
| 2 | Ana Silva | Ana | |
| 3 | Ben Lee Jones | ||
| 4 | Cher |
In C2: =TEXTBEFORE(A2, " ") → Ana
How it works
- TEXTBEFORE returns the part of the text that comes before a separator: here, a space.
- TEXTAFTER returns the part that comes after it.
- Both need Microsoft 365. The versions below work everywhere.
Variations
The last name
A B C 1 Full name 2 Ana Silva Silva 3 Ben Lee Jones 4 Cher In C2:
=TEXTAFTER(A2, " ")→ SilvaThe last name of someone with three names
A B C 1 Full name 2 Ana Silva Jones 3 Ben Lee Jones 4 Cher In C2:
=TEXTAFTER(A3, " ", -1)→ JonesThe -1 counts spaces from the end, so only the last word is returned.
First name, in any version of Excel
A B C 1 Full name 2 Ana Silva Ana 3 Ben Lee Jones 4 Cher In C2:
=LEFT(A2, FIND(" ", A2)-1)→ AnaFIND gives the position of the space; LEFT takes the characters before it.
Last name, in any version of Excel
A B C 1 Full name 2 Ana Silva Silva 3 Ben Lee Jones 4 Cher In C2:
=MID(A2, FIND(" ", A2)+1, LEN(A2))→ SilvaBoth at once, in two cells
A B C D 1 Full name 2 Ana Silva Ana Silva 3 Ben Lee Jones 4 Cher In C2:
=TEXTSPLIT(A2, " ")→ Ana, SilvaThe pieces spill into the cells to the right.
Watch out for
- A name with no space.
=TEXTBEFORE(A4, " ")on "Cher" gives #N/A, because there is no space to split at. Fall back on the whole name:=IFERROR(TEXTBEFORE(A4, " "), A4). - Middle names. Splitting at the first space puts middle names with the last name. Splitting at the last space, with -1, puts them with the first.
- Without a formula. Data > Text to Columns splits a column at spaces in one go, and Ctrl+E (Flash Fill) copies a pattern you start by hand.
Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.
Functions used
- TEXTBEFORE: The text that comes before a delimiter.
- TEXTAFTER: The text that comes after a delimiter.
- TEXTSPLIT: Splits text into separate cells at a delimiter. The result spills.
- LEFT: The first characters of a text value.
- MID: Characters from the middle of a text value.
- FIND: Position of one text inside another (case-sensitive).