Names
How to Split Full Names into First and Last Name in Excel
For a clean list, type the first name next to the first row and press Ctrl+E (Flash Fill), or select the column and use Data, Text to Columns with a space as the delimiter. For a formula, use =LEFT(A2,FIND(" ",A2)-1) for the first name and =TRIM(MID(A2,FIND(" ",A2),LEN(A2))) for the rest. Check the result for names with more than two parts: simple splits get those wrong.
Names look easy until you split thousands of them. "John Smith" splits cleanly, but "Mary Ann O'Brien", "Dr. Ravi Kumar", "Maria de la Cruz" and "Li" do not. This guide covers the three quick methods, what each one gets wrong, and how to check your result.
Method 1: Flash Fill (fastest for small lists)
- Put the full names in column A. In B2, type the first name exactly as it should appear for A2.
- In B3, start typing the next first name. Excel shows a grey preview of the pattern.
- Press Enter to accept it, or press Ctrl+E on B3 to fill the whole column.
Flash Fill guesses from your examples, so look at every unusual row. It does not update if the source names change later.
Method 2: Text to Columns (no formulas)
- Select the full-name column.
- Choose Data, then Text to Columns, then Delimited, then Next.
- Tick Space, untick the others, and pick a destination column so you do not overwrite the originals.
A name with three words becomes three columns. That is correct for "Mary Ann Smith" and wrong for "Maria de la Cruz", where the last name is three words.
Method 3: formulas (updates when the names change)
First name (everything before the first space):
=IFERROR(LEFT(A2,FIND(" ",A2)-1),A2)Last name (everything after the last space), works in every version of Excel:
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))Everything after the first space (the middle and last names together):
=IFERROR(TRIM(MID(A2,FIND(" ",A2)+1,LEN(A2))),"")In Microsoft 365 you can also use =TEXTSPLIT(A2," "), which spills each word into its own cell.
The names that break simple splits
| Name | Problem | What to do |
|---|---|---|
Mary Ann Smith | Is "Mary Ann" the first name? | Decide a rule: first word is first name, last word is last name |
Maria de la Cruz | The last name has three words | Review by hand, or keep the whole tail as the last name |
Dr. Ravi Kumar, Mr John Smith | The title lands in the first-name column | Remove titles first with Find and Replace |
John Smith Jr. | The suffix becomes the last name | Move suffixes into their own column |
Madonna | One word, no space | The IFERROR in the formulas above keeps it |
Smith, John | Surname first, comma-separated | Split on the comma instead of the space |
Warning: no formula can know a person's name. If the split matters (letters, invoices, legal documents), review every row that does not have exactly two words.
Fix the capitals afterwards
Names typed in capitals or lowercase are common in exports. =PROPER(A2) capitalises each word, and it handles apostrophes well (o'brien becomes O'Brien). It does not know about McDonald, van der Berg or DeShawn, which it turns into Mcdonald, Van Der Berg and Deshawn. Check those by eye.
When there are too many rows to review by eye
DecodingPro tidies capitals and spacing across a whole file and flags names it is not sure about instead of rewriting them.
You start with 100 free credits. Every change is logged, and anything unclear is flagged, never guessed.
Fix a spreadsheetQuestions people ask
Why does Flash Fill not appear?
Flash Fill needs Excel 2013 or later, and it must be on under File, Options, Advanced, "Automatically Flash Fill". It also only triggers when the pattern is clear from the cells next to the column.
Can I split names in a CSV without opening Excel?
Yes. Any spreadsheet tool can do it, and so can a short script, but the same edge cases apply: titles, suffixes and multi-word last names need a rule.
How do I join first and last names back together?
Use =B2&" "&C2, or =TEXTJOIN(" ",TRUE,B2:C2) which skips empty cells.