How to Split Full Names Into First and Last Name Columns

Two formulas split most names correctly.

First:  =LEFT(TRIM($A2), FIND(" ", TRIM($A2))-1)
Last:   =TRIM(RIGHT(SUBSTITUTE(TRIM($A2), " ", REPT(" ",100)), 100))

The word "most" is doing real work in that sentence, and the last section of this guide is about the ones it gets wrong — because a split you haven't checked is worse than no split at all.

Eight rows split correctly and are left alone. Six are flagged for a human — five of them because the answer is wrong.

The three ways, and when each is right

Method	Good for	Catch
Text to Columns	A one-off split you'll never repeat	Overwrites the columns to the right, permanently
Flash Fill (Ctrl+E)	A quick look at messy data	Guesses, and doesn't update when the source changes
Formulas	Anything the list will keep growing into	None worth mentioning

Text to Columns

Data → Text to Columns → Delimited → Space → Finish. Ten seconds, and it destroys whatever is in the columns to the right without asking twice.

Insert two empty columns first. Every time. It's a one-way operation and Undo is the only route back.

Flash Fill

Type the first name in the column beside your data, then press Ctrl+E. Excel infers the pattern and fills the rest. It's genuinely clever and it is a guess — it will find a pattern in your first two examples that isn't the one you meant, and it fills static text that won't change when the names do.

Useful to see what you're dealing with. Not something to build on.

The formulas, in order of what they handle

First name

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

FIND returns the position of the first space. LEFT takes everything before it — hence the -1, which stops the space itself coming along.

TRIM wraps both because a leading space would otherwise make FIND return 1, and LEFT(...,0) gives you nothing at all.

Last name

=TRIM(RIGHT(SUBSTITUTE(TRIM($A2), " ", REPT(" ",100)), 100))

This looks absurd and it's the standard solution, so it's worth understanding rather than pasting.

Reading from the inside: SUBSTITUTE replaces every space with a hundred spaces. "Ravi Kumar Patel" becomes "Ravi" + 100 spaces + "Kumar" + 100 spaces + "Patel". RIGHT(...,100) then grabs the final hundred characters, which — since the last word is shorter than a hundred — is the last word plus a lot of padding. TRIM removes the padding.

The result is the last word, however many words came before it. A simpler RIGHT and FIND version only works when there are exactly two words.

⚠️ The number 100 must exceed the longest single word in your data. It does. But if you're splitting product codes or addresses rather than names, check.

Single-word entries

One entry with no space — a company in a person column, or someone with one name — makes FIND return #VALUE! and the error spreads. Wrap it:

=IFERROR(LEFT(TRIM($A2), FIND(" ",TRIM($A2))-1), TRIM($A2))

Note what the fallback returns: the whole entry, not a blank. A single-name person has a name, and it belongs in the column rather than vanishing.

If you have Microsoft 365

First: =TEXTBEFORE(TRIM($A2), " ")
Last:  =TEXTAFTER(TRIM($A2), " ", -1)

The -1 means "the last occurrence", which is what makes the REPT trick unnecessary. These don't exist in Excel 2021 or earlier, or in a file anyone on an older version will open, which is why the longer versions are still the ones to know.

The part most guides skip

Names do not split reliably. Not "usually work with a few exceptions" — the exceptions are common enough that a split you haven't reviewed will contain errors.

In the fourteen names above, five come out wrong:

Name	Formula says	Should be
Tom van der Berg	Berg	van der Berg
Luis De La Cruz	Cruz	De La Cruz
Ahmed Ben Ali	Ali	Ben Ali
Dr Nadia Fenwick	First name: Dr	Nadia
James Ashworth Jr	Last name: Jr	Ashworth

Multi-part surnames, titles and suffixes. Add to that people with two family names, people with one name, and orderings where the family name comes first, and no formula is going to sort this out on its own.

So flag them instead

You can't fix the hard cases automatically. You can find them:

=LEN(TRIM($A2)) - LEN(SUBSTITUTE(TRIM($A2), " ", "")) + 1

Length with spaces, minus length without them, gives the number of spaces. Add one and you have the word count.

Anything with two parts is almost certainly fine. Anything with three or more needs eyes on it. In the example that's six rows out of fourteen — and of those six, five are wrong and one (Ravi Kumar Patel) is perfectly correct.

That's the honest workflow: split everything, count the parts, colour anything above two, and read those rows yourself. On a list of two thousand names you might be checking eighty. That's twenty minutes, and it's twenty minutes rather than an afternoon or a mailshot addressing someone as Dear Jr.

⚠️ Never delete the original column. It's the only record of what the name actually was, and you will need it the first time someone queries their own entry.

Other formats you'll meet

"Whitfield, Sarah"

First: =TRIM(MID($A2, FIND(",",$A2)+1, 100))
Last:  =TRIM(LEFT($A2, FIND(",",$A2)-1))

A comma is a far better separator than a space, because there's normally only one. If you get to choose how names are stored, this format is the one to ask for.

Middle names, when you want them

Everything between the first space and the last:

=TRIM(MID($A2, LEN($B2)+1, LEN($A2)-LEN($B2)-LEN($C2)))

Referencing the First and Last columns you already made, rather than recalculating them. Blank for two-part names, which is correct.

Does this work in Google Sheets?

Yes — LEFT, RIGHT, MID, FIND, LEN, TRIM, SUBSTITUTE, REPT and IFERROR all behave identically.

Sheets also has SPLIT, which Excel lacks: =SPLIT(A2, " ") spills the parts across as many columns as it needs. Convenient, and it makes uneven column counts more likely rather than less, so the word-count check still applies.

When it goes wrong

#VALUE! on some rows

Those entries have no space. Wrap in IFERROR with the whole value as the fallback.

The first name comes back empty

A leading space. TRIM inside FIND, not just around the result.

Two names look identical but split differently

One has a double space, or a non-breaking space pasted from a web page. TRIM handles doubles; non-breaking spaces need SUBSTITUTE(A2, CHAR(160), " ") first, and they're invisible until you go looking.

The results changed when I sorted

The formulas reference their own row, so sorting is safe — unless you sorted the name column only, without the formula columns. Convert to values before any restructuring you're unsure about.

Text to Columns wiped a column of data

It does that. Ctrl+Z immediately, before doing anything else.

Common questions

Should I split names at all?

Only if you need the parts separately — sorting by surname, or a mail merge that says "Dear Sarah". If you just need to display the name, leave it whole. Every split is a chance to get someone's name wrong.

How do I capitalise them properly?

=PROPER(A2) capitalises each word, and it will render "McDonald" as "Mcdonald" and "o'connor" as "O'Connor" — one right, one wrong. Use it as a starting point, not a finish.

What about email addresses like sarah.whitfield@…?

Same technique, with "." and "@" as separators instead of spaces. Reconstructing names from emails is guesswork with a worse hit rate than splitting.

Can I do this without helper columns?

You can nest it all into one formula. Don't — you lose the ability to see which step went wrong, and the word-count column is the most useful part of the whole approach.

How do I turn the formulas into plain text afterwards?

Select the columns, copy, then Paste Special → Values over the top. Do this before deleting anything the formulas depend on.

The short version

• First name: LEFT up to the first space, wrapped in TRIM and IFERROR.
• Last name: the SUBSTITUTE/REPT trick returns the final word whatever came before it.
• Count the words. Two is safe; three or more needs a human.
• Multi-part surnames, titles and suffixes are common, not edge cases.
• Keep the original column. Always.
• Text to Columns overwrites what's to the right. Insert blank columns first.
• Flash Fill guesses and doesn't update. Fine for looking, not for building.

Next steps: the same TRIM problems that break a name split are what stop two lists matching, and once names are split you'll find the duplicates you couldn't see before — highlight them before deleting anything.