exceltestpracticeStart free
Practice testsLearnFunctionsShortcutsPricingStart free

How-to guides

How to split full names into first and last name in Excel

Break a Full Name column into First and Last columns with a formula that keeps working, and handle middle names that break the simple version.

A Full Name column cannot be sorted by last name, mail-merged with a first name or matched against another list until it is split. The job is to find the space and take the text on each side of it.

The two-word case takes three functions. The real work is deciding what to do about the names that are not two words.

Steps in Excel for Windows

Shortcut: Ctrl+E (Flash Fill)

  1. Look at the data first. Scan the column for middle names, initials, suffixes such as Jr. and names with extra spaces. The simple formulas below assume exactly one space between the first and last name.
  2. Get the first name. In B2 type =LEFT(A2,FIND(" ",A2)-1). FIND returns the position of the first space, and LEFT takes everything before it.
  3. Get the last name. In C2 type =RIGHT(A2,LEN(A2)-FIND(" ",A2)). LEN minus the position of the space gives the number of characters after it.
  4. Fill down. Double-click the fill handle at the bottom-right corner of each formula cell, or select both cells and drag down.
  5. For middle names, take the last word. Use =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),50)). It pads every space to 50 spaces, takes the last 50 characters and trims, which leaves only the final word.
  6. Alternatives. Text to Columns with Space as the delimiter splits once without formulas. Flash Fill (Ctrl+E) copies a pattern from one or two typed examples. Both give plain text that does not update.

Menu names are from Microsoft 365 on Windows. Excel for Mac and Excel for the web put the same commands in slightly different places.

Worked example

1. Split with Text to Columns

Before
A
1Name
2Ana Ruiz
3Kim Lee
4Raj Patel
After
AB
1Name
2AnaRuiz
3KimLee
4RajPatel

With Space ticked as the delimiter, each full name in column A splits into a first name in A and a last name in B. Names with a middle name would spill into a third column, so check the data first.

2. First name with LEFT and FIND

First name with LEFT and FIND. The formula =LEFT(A2,FIND(" ",A2)-1) in cell B2 returns Ana.
ABC
1Full nameFirstLast
2Ana RuizAna

FIND(" ",A2) is 4, the position of the space after Ana. LEFT takes the 3 characters before it.

3. Last name with RIGHT, LEN and FIND

Last name with RIGHT, LEN and FIND. The formula =RIGHT(A2,LEN(A2)-FIND(" ",A2)) in cell C2 returns Ruiz.
ABC
1Full nameFirstLast
2Ana RuizRuiz

The name has 8 characters and the space is character 4, so RIGHT takes the last 4: Ruiz.

4. What a middle name does to the simple formula

What a middle name does to the simple formula. The formula =RIGHT(A2,LEN(A2)-FIND(" ",A2)) in cell C2 returns Maria Ruiz.
ABC
1Full nameFirstLast
2Ana Maria RuizMaria Ruiz

In A2 the first space comes after Ana, so everything after it is taken, middle name included. The result is Maria Ruiz, not Ruiz.

5. The last word, however many names come before it

The last word, however many names come before it. The formula =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),50)) in cell C2 returns Ruiz.
ABC
1Full nameFirstLast
2Ana Maria RuizRuiz

SUBSTITUTE swaps each space for 50 spaces, RIGHT keeps the last 50 characters, and TRIM removes the padding.

Common mistakes

  • Assuming one space. A middle name or a two-word last name such as de la Cruz changes where the split belongs. Decide a rule, for example the last word is the last name, and test it on the awkward rows.
  • Extra spaces in the source. Leading, trailing or doubled spaces move the position FIND returns. Wrap the source in TRIM, as in TRIM(A2), before extracting.
  • Forgetting the minus one. LEFT(A2,FIND(" ",A2)) includes the space itself in the result. Subtract 1 to stop before it.
  • A name with no space. A one-word name makes FIND return #VALUE!. Wrap it in IFERROR if the list can hold single names.
  • Using Text to Columns on a live list. The result is static text. If new rows keep arriving, formulas are the better choice.

On Excel job tests

Wording to watch: "separate the full name into first name and last name columns". If the sample has only two-word names, LEFT and RIGHT with FIND are enough. A sample with a middle name or a hyphenated surname is a hint that the simple version is not.

Try your formulas on the row with the longest name before filling down, since that row is the most likely to break them.

  • LEFTReturns a number of characters from the start of text.
  • RIGHTReturns a number of characters from the end of text.
  • FINDReturns the position of one text string inside another, matching case exactly.
  • LENReturns the number of characters in text.
  • TRIMRemoves extra spaces from text, leaving single spaces between words.
  • SUBSTITUTEReplaces specific text inside a string with new text.
  • use Text to Columns in ExcelSplit text in one column into separate columns at a comma, space, tab or other delimiter, with the Text to Columns wizard on the Data tab.
  • combine first and last names in ExcelJoin a first-name column and a last-name column into one full name, with the right spacing, order and handling for missing parts.

Questions

How do I split a full name without formulas?
Select the column and use Data, Text to Columns with Space as the delimiter. Flash Fill with Ctrl+E also works from a typed example.
How do I get only the first name?
Use =LEFT(A2,FIND(" ",A2)-1). It returns everything before the first space.
How do I get the last name when there is a middle name?
Take the last word with =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),50)).
Is there a shorter way in newer Excel?
Microsoft 365 has TEXTBEFORE and TEXTAFTER, which return the text before or after a delimiter in one step.

Practice this

No free lesson covers splitting names yet. The closest practice is the free 5-item sampler, which uses the same kind of tasks in a working grid.

Try a 5-item Excel test sample