Split First and Last Names
There is no single right way to split a name, only a right way for the list in front of you. Four methods compete for the job — a menu command, a pair of formulas, Flash Fill, and the modern TEXTBEFORE and TEXTAFTER — and which of them wins turns entirely on whether every row really is two words, something nothing in Excel will tell you. That makes the useful work a verification step rather than a formula choice. Put =LEN(A2)-LEN(SUBSTITUTE(A2," ","")) in a spare column to count the spaces in each name and filter it to anything above one, and you have isolated the precise rows — middle names, initials, particles like van der, generational and academic suffixes — where every one of the four methods will need a human to look. On a genuinely two-word list they all agree and none of this matters; the whole difficulty lives in that filtered subset, and knowing how big it is before you start is worth more than any formula on this page.
The formula
=IFERROR(LEFT(A2,FIND(" ",A2)-1),A2)
first name — the text before the first space; IFERROR covers a one-word cell
=IFERROR(RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))),A2)
last name — LEN minus LEN-without-spaces counts the spaces, SUBSTITUTE marks
that last one with an asterisk, FIND locates it, RIGHT keeps what follows
=IFERROR(TRIM(MID(A2,FIND(" ",A2)+1,FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-FIND(" ",A2))),"")
everything between the two — middle names and initials, empty for a plain pair
=TEXTBEFORE(A2," ",1,,,A2) first name, Excel 365 / 2024; the last argument is the if-not-found fallback
=TEXTAFTER(A2," ",-1,,,A2) last name; -1 means "the last space", not the first
=LEFT(A2,FIND(",",A2)-1) surname out of "Doe, Jane"
=TRIM(MID(A2,FIND(",",A2)+1,LEN(A2))) and the given name from that same cellA worked example
A2:A6 holds five names in one column: "John Smith", "Grace Brewster Hopper", "Cher", "Jan van der Berg" and "Martin Luther King Jr".
The two IFERROR formulas above in B2 and C2, filled down to row 6.
John/Smith and Grace/Hopper come out right, and Cher comes back as both first and last — that is the IFERROR earning its place, because without it FIND cannot locate a space and both formulas return #VALUE! rather than the single name. The last two rows are wrong in the way every name splitter is wrong: "Jan van der Berg" yields a surname of "Berg" instead of "van der Berg", and "Martin Luther King Jr" yields "Jr". Those rows are findable, though — put =LEN(A2)-LEN(SUBSTITUTE(A2," ","")) in a helper column to count the spaces and filter it to anything above 1, and you have every row the formulas could have mishandled.
Which one do I need?
| If you want to… | Use |
|---|---|
| Only the first name, on any version of Excel | =IFERROR(LEFT(A2,FIND(" ",A2)-1),A2) — the wrapper is what stops a one-word cell returning #VALUE! |
| Only the surname, where some rows carry a middle name and others do not | The SUBSTITUTE-and-RIGHT formula above, which locates the final space rather than the first |
| You are on Excel 365 or Excel 2024 | =TEXTBEFORE(A2," ") and =TEXTAFTER(A2," ",-1) replace the whole construction; add a sixth argument as the if-not-found fallback for one-word cells |
| You would rather not write a formula at all | Type the first row by hand and press Ctrl+E — Flash Fill infers the pattern and is the one method that copes with mixed formats in a single pass |
| A one-off split of a tidy two-word list | Data > Text to Columns > Delimited > Space, faster but static, and it overwrites the source column |
| The cell reads "Doe, Jane" with the surname first | =LEFT(A2,FIND(",",A2)-1) for the surname and =TRIM(MID(A2,FIND(",",A2)+1,LEN(A2))) for the given name; the space-based formulas would hand back "Doe," with the comma still attached |
| A middle name or initial needs its own column | The MID formula above returns everything between the first and last space, so "Grace Brewster Hopper" gives "Brewster" and a plain two-part name gives an empty cell |
| The list contains van der Berg, De La Cruz, or academic and generational suffixes | No formula gets these right. Count the spaces with =LEN(A2)-LEN(SUBSTITUTE(A2," ","")), filter to more than one, and fix that subset by hand |
| Results look shifted by a character, or a surname arrives with a leading space | Double spaces and trailing spaces move every FIND position along — run the source column through TRIM before splitting anything |
| You need to put the parts back into one cell | The reverse job, with its own trap around deleting the source columns afterwards |
Frequently asked questions
Which method should I use — a menu command, a formula, or Flash Fill?
Count the spaces first and let the answer decide. If every row has exactly one, all four methods agree and the fastest wins. If some rows have two or more, drop the menu command immediately, because it distributes each piece into its own column and shifts the surname one place right on those rows only. Between the survivors, formulas re-run when the source changes and Flash Fill produces static text from a pattern it guessed, which is why its output has to be read rather than trusted.
Why does my last name formula return #VALUE! on some rows?
Those rows hold a single word with no space in them — a mononym, an organisation sitting in a person column, or a cell that only looks blank. FIND reports #VALUE! when the space it was sent to look for is absent, and SUBSTITUTE does the same when asked for occurrence number zero. Wrapping the whole thing in IFERROR and falling back to the original cell keeps the column usable and makes those rows easy to spot.
How do I handle surnames like van der Berg or De La Cruz?
Not with a rule, because no rule can exist. Any logic that keeps "van der Berg" together as one surname also glues the middle name onto "Mary Anne Wilson", since the text itself carries nothing that distinguishes a particle from a given name. The workable approach is to split mechanically, isolate the multi-space rows with a space count, and correct that subset by hand — a list of particles helps you sort them, but it will never decide them.
How do I check a name split rather than just trusting it?
Two checks, and they catch different faults. =TRIM(B2&" "&C2&" "&D2)=TRIM(A2), filled down beside the split columns, returns FALSE wherever characters were lost or duplicated — a dropped comma, a one-word row echoed into two columns, a Flash Fill guess that quietly failed on row 400. It cannot see a piece placed in the wrong column, since "Jan | van der | Berg" reassembles perfectly, so pair it with the space count, which is what flags those rows for review.
New guides and tools, once a month
DE + EN · double opt-in · no spam