What is the quickest way to split names in Excel?

For a one-off job, Flash Fill is quickest: type the first name for the first row in the next column, press Enter, then press Ctrl+E. Excel fills the rest by copying your pattern. Check a few rows by eye afterwards, because Flash Fill guesses and can get middle names wrong.

How do I split names in Google Sheets?

Put =SPLIT(A2," ") in the next column and fill it down. Each word of the name goes into its own column. Google Sheets also has Data > Split text to columns, which does the same thing without a formula.

How do I get the surname when some people have a middle name?

Take everything after the last space rather than the second word. In Excel 365 use =TEXTAFTER(A2," ",-1). The -1 tells Excel to count from the end, so "Mary Jane Smith" gives "Smith".

What about double-barrelled surnames like Lloyd-Webber?

Hyphenated surnames are fine, because the formulas split on spaces, not hyphens. The tricky ones are unhyphenated two-word surnames such as "de Souza" or "Van Dyke". No formula can tell those apart from a middle name, so filter for rows with three or more words and check them by hand.

Will splitting names delete my original data?

Text to Columns overwrites the cells to the right of your name column, so insert two empty columns first or work on a copy of the sheet. The formula methods leave the original column untouched.

Guide · Spreadsheets

How to split first and last names in Excel and Google Sheets

You have a column of full names and need first name and surname in separate columns, maybe for a mail merge, a CRM import or Christmas card labels. Here are four ways to do it, from a two-second shortcut to formulas that cope with middle names, plus the messy cases to check before you send anything.

Rather send it to us? Up to 500 rows cleaned for £15

Before you start

Work on a copy of the sheet, or insert two empty columns to the right of the names.
Tidy stray spaces first. In Excel or Google Sheets, =TRIM(A2) removes double spaces and spaces at the start or end, which otherwise create blank "names".
Look for titles (Mr, Mrs, Dr) and suffixes (Jr, OBE). Decide whether you want them in their own column.

Method 1: Flash Fill (Excel, quickest)

In B2, type the first name exactly as it appears in A2.
Press Enter, then press Ctrl+E (or Data > Flash Fill).
Do the same in C2 with the surname.

Flash Fill copies the pattern you typed. It is fast but it guesses, so scan the results for anyone with a middle name or a title.

Method 2: Text to Columns (Excel) or Split text to columns (Google Sheets)

Select the name column.
In Excel: Data > Text to Columns > Delimited > tick Space > Finish. In Google Sheets: Data > Split text to columns, then choose Space as the separator.

Every word goes into its own column, so "Mary Jane Smith" fills three columns. That is fine for simple lists, but middle names push surnames into the wrong column.

Method 3: formulas that handle middle names (Excel 365)

First name: =TEXTBEFORE(A2," ")
Surname: =TEXTAFTER(A2," ",-1) (the -1 means "after the last space")
Middle name(s): =IFERROR(TEXTBEFORE(TEXTAFTER(A2," ")," ",-1),"")

On older versions of Excel use =LEFT(A2,FIND(" ",A2)-1) for the first name and =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)) for the surname.

Method 4: SPLIT in Google Sheets

Put =SPLIT(TRIM(A2)," ") in B2 and fill down. For surname only, use =REGEXEXTRACT(TRIM(A2),"[^ ]+$"), which takes the last word.

The messy cases to check by hand

Two-word surnames without a hyphen such as "de Souza", "Van Dyke" or "St John". No formula can tell these from a middle name.
Titles and suffixes: "Dr Sarah Patel" puts "Dr" in the first name column.
Couples: "John and Sue Brown" needs a decision on how you address them.
Surname first: lists exported as "Smith, John" need splitting on the comma instead.
Single names or company names in the name column.

A quick way to find them: add =LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1 to count the words in each name, then filter for 3 or more.

Frequently asked questions

What is the quickest way to split names in Excel?: For a one-off job, Flash Fill is quickest: type the first name for the first row in the next column, press Enter, then press Ctrl+E. Excel fills the rest by copying your pattern. Check a few rows by eye afterwards, because Flash Fill guesses and can get middle names wrong.
How do I split names in Google Sheets?: Put =SPLIT(A2," ") in the next column and fill it down. Each word of the name goes into its own column. Google Sheets also has Data > Split text to columns, which does the same thing without a formula.
How do I get the surname when some people have a middle name?: Take everything after the last space rather than the second word. In Excel 365 use =TEXTAFTER(A2," ",-1). The -1 tells Excel to count from the end, so "Mary Jane Smith" gives "Smith".
What about double-barrelled surnames like Lloyd-Webber?: Hyphenated surnames are fine, because the formulas split on spaces, not hyphens. The tricky ones are unhyphenated two-word surnames such as "de Souza" or "Van Dyke". No formula can tell those apart from a middle name, so filter for rows with three or more words and check them by hand.
Will splitting names delete my original data?: Text to Columns overwrites the cells to the right of your name column, so insert two empty columns first or work on a copy of the sheet. The formula methods leave the original column untouched.

Want it done for you?

Send us your spreadsheet and we split the names, fix titles and two-word surnames by hand, remove duplicates and tidy spacing and capitals. Up to 500 rows for £15, or up to 10,000 rows for £60, sent back by email. You pay by a one-off Stripe link and we only work with the data you send.

Get your spreadsheet cleaned · from £15

Written by TCH Works, Harlow, Essex, October 2026.