How to Split First and Last Names in Excel

To split names in Excel into first and last name, split the text at the space: TEXTBEFORE gives the part before it and TEXTAFTER the part after it. In older versions, LEFT, RIGHT and FIND do the same job, and Text to Columns or Flash Fill work without any formula. This guide is for anyone tidying a contact or employee list, and it covers middle names, "Last, First" lists and the names that break simple formulas.

Which method should you use to split names?#

Pick a formula if the list will change, and a tool if it's a one-off job. Here are the options:

The examples use these names in column A:

Clean the names first with TRIM#

Extra spaces break every splitting method, so clean the names before you split them. A name like " Dana Whitfield" has spaces at the start and two in the middle; splitting at the first space gives an empty first name. TRIM removes spaces at both ends and squeezes the rest down to one:

=TRIM(A2)

Use the cleaned column as the source for your split formulas, or nest TRIM inside them. The TRIM function guide has more examples, and how to clean messy data covers capitals and hidden characters too.

How to split names in Excel with TEXTBEFORE and TEXTAFTER#

TEXTBEFORE returns the text before a delimiter, and TEXTAFTER the text after it. The delimiter here is a space, written " ". For the first name, in B2:

=TEXTBEFORE(A2," ")

For the last name, in C2, split at the last space with -1:

=TEXTAFTER(A2," ",-1)

For Marco Luca Bellini, the results are Marco and Bellini. The -1 matters: =TEXTAFTER(A2," ") splits at the first space and returns "Luca Bellini". Fill both formulas down (Windows: Ctrl+D; Mac: ⌘D).

How to get the middle name#

The middle name is what's left between the first and last spaces. Take everything after the first space, then everything before its last space:

=TEXTBEFORE(TEXTAFTER(A2," ")," ",-1,0,0,"")

For Marco Luca Bellini this returns Luca. For Dana Whitfield there's no second space, so the last argument, "", returns empty text instead of #N/A. See TEXTBEFORE and TEXTAFTER for all the arguments.

How to split names in older Excel with LEFT and FIND#

In Excel 2021 and older, find the space with FIND, then take the text around it. FIND returns the position of the space; for Dana Whitfield, =FIND(" ",A3) gives 5. The first name is one character shorter than that:

=LEFT(A3,FIND(" ",A3)-1)

For a two-part name, the last name is everything after the space. LEN counts all the characters, so subtract the space's position:

=RIGHT(A3,LEN(A3)-FIND(" ",A3))

Both return Dana and Whitfield. To understand the pieces, read how FIND and SEARCH find text in a cell and LEFT, RIGHT and MID explained; the LEFT function guide is a quick reference.

Last name with a middle name, in older Excel#

Without TEXTAFTER, getting the text after the last space takes a longer formula:

=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

Read it from the inside out. LEN(A2)-LEN(SUBSTITUTE(A2," ","")) counts the spaces (2 for Marco Luca Bellini). SUBSTITUTE then swaps only that last space for an @, FIND gives its position (11), and RIGHT takes the 7 characters after it: Bellini. It works as long as the names don't contain @.

How to split "Last, First" names#

When names are stored as "Bellini, Marco", split at the comma instead. Include the space after the comma, or every first name will start with a space:

=TEXTAFTER(A2,", ")

That returns the first name, and =TEXTBEFORE(A2,",") returns the last name. In older Excel, use =LEFT(A2,FIND(",",A2)-1) for the last name and =MID(A2,FIND(",",A2)+2,LEN(A2)) for the first. The +2 skips both the comma and the space. To put the parts back as "Marco Bellini", join them with &; see how to combine text in Excel.

How to split names without formulas#

Text to Columns and Flash Fill split names in a few clicks, but the results are plain values. If a name changes later, you have to split it again.

Text to Columns:

1. Select the names, for example A2:A5.
2. Go to Data > Text to Columns.
3. Choose Delimited, click Next, tick Space, and click Finish.

It overwrites the columns to the right, so make sure they're empty. A middle name ends up in its own column.

Flash Fill: type the first name for the first row (Marco in B2), move to B3, and press Ctrl+E on Windows, or use Data > Flash Fill on a Mac. Excel follows your example for the rest of the column. Check the results, since it guesses the pattern.

In Microsoft 365 there's also TEXTSPLIT: =TEXTSPLIT(A2," ") spills each part of the name into its own column.

Names that need a manual check#

No formula understands names, so check the results by eye. Watch for:

• Single names, like "Cher": there's no space, so the formulas return #VALUE! or #N/A. Wrap them in IFERROR: =IFERROR(TEXTBEFORE(A2," "),A2).
• Suffixes, like "Samuel Okafor Jr.": the last name comes out as Jr.
• Multi-word surnames, like "Ana de la Cruz": splitting at the last space gives Cruz, not de la Cruz.

For a short list, fix these by hand after the split. For a long list, flag rows with more than one space using =LEN(A2)-LEN(SUBSTITUTE(A2," ",""))>1 and review them.

Practise it#

Practise TEXTBEFORE and TEXTAFTER in a live spreadsheet: split emails, flip "Last, First" names and use -1 for the last delimiter. Then practise LEFT, RIGHT, MID and LEN to build the older-Excel versions of the same formulas.