How to Split Names in Excel
Turn one column of full names into separate First Name and Last Name columns — with Text to Columns, a formula, Flash Fill, or a single click.
Last updated: August 2026
| Full Name | First Name | Last Name | |
|---|---|---|---|
| Amina Al Khalifa | → | Amina | Al Khalifa |
| John Robert Smith | → | John Robert | Smith |
| Okafor, Chidi | → | Chidi | Okafor |
Method 1 — Text to Columns (built into Excel)
Best when names are simply First Last separated by a space. It's fast but it splits on every space, so middle names spill into extra columns.
- Select the column of names. If the data to the right isn't empty, insert two blank columns first so nothing gets overwritten.
- Go to Data → Text to Columns and choose Delimited, then Next.
- Tick Space as the delimiter (untick the others) and click Next.
- Set the Destination to the first empty column, click Finish, and rename the columns First Name and Last Name.
Method 2 — Formulas (keeps the original column)
Use formulas when you want the split to stay linked to the source. For a name in A2:
- First name:
=LEFT(A2, FIND(" ", A2) - 1) - Last name:
=MID(A2, FIND(" ", A2) + 1, LEN(A2))— or, to capture everything after the first space including middle names,=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))for just the last word. - Copy the formulas down the column. Convert to values (Paste Special → Values) if you no longer need them linked.
Formulas get fiddly fast with two-word surnames (Al Khalifa, van der Berg), suffixes (Jr.), and Last, First order.
Method 3 — Flash Fill (Excel 2013+)
Type the first name for the first row, then the second, and press Ctrl + E. Excel guesses the pattern. It's quick but non-deterministic — it can silently mis-split edge cases, and there's no preview of what changed.
The 1-click way — Tellsheet's Split Names
Select the name column, click Split Names, and Tellsheet writes clean First and Last columns beside your data — middle names stay with the first name, and Last, First order is detected automatically. You see a full before/after preview, and every change is revertable. Nothing leaves your computer.
Frequently asked questions
How do I split a name with a middle name?
Text to Columns puts each space-separated word in its own column, so a middle name lands in a third column you'll need to merge back. Tellsheet's Split Names keeps the middle name with the first name by default, giving you clean First and Last columns.
How do I split names in "Last, First" format?
Set the comma as the delimiter in Text to Columns — that puts the surname first, so you'll swap the columns after. Tellsheet detects the comma automatically and returns the given name and surname already in the right order.
How do I keep the original name column?
In Text to Columns, point the destination at an empty cell to the right so the original stays intact. Tellsheet inserts the new columns beside your data and never overwrites the source.
Does this work on Excel for Mac and the web?
Yes. Text to Columns and formulas work everywhere. Tellsheet runs on Excel for Windows, Mac, and the web through the same add-in.
Related Excel guides
Split names in one click
Get Tellsheet free and clean up names, dates, duplicates, and more — right inside Excel, with a preview before every change.
Get Tellsheet free See pricing