How to Split Text into Columns in Excel (3 Easy Ways)
You've been sent a list where full names are all in one column — "Jane Smith", "Tom Jones" — but you need first and last names separately. Or addresses crammed into one cell with commas. Excel can split them in seconds.
Method 1: Text to Columns
The classic, works in every version of Excel.
Before you start: Text to Columns writes the results into the columns to the right, overwriting anything there. Insert some empty columns first (right-click the column letter to the right > Insert).
1. Select the column with the text (for example, click column A).
2. Go to Data > Text to Columns.
3. Choose Delimited and click Next. ("Delimited" means the parts are separated by a character like a space or comma.)
4. Tick the separator — Space for names, Comma for addresses, or Other and type a character like - or /.
5. Check the preview at the bottom, then click Next.
6. Optionally, click a column in the preview and set its format. Choose Text for things like phone numbers or codes with leading zeros.
7. Click Finish.
2. Go to Data > Text to Columns.
3. Choose Delimited and click Next. ("Delimited" means the parts are separated by a character like a space or comma.)
4. Tick the separator — Space for names, Comma for addresses, or Other and type a character like - or /.
5. Check the preview at the bottom, then click Next.
6. Optionally, click a column in the preview and set its format. Choose Text for things like phone numbers or codes with leading zeros.
7. Click Finish.
Tip: If some names have middle names, they'll spill into an extra column. Tick Treat consecutive delimiters as one to handle double spaces.
Method 2: Flash Fill (the quickest)
Flash Fill watches what you type and copies the pattern.
1. In the column next to your names, type the first person's first name exactly as you want it.
2. Click the cell below.
3. Press Ctrl + E (or go to Data > Flash Fill).
2. Click the cell below.
3. Press Ctrl + E (or go to Data > Flash Fill).
Excel fills in every first name. Do the same in the next column for surnames.
Flash Fill is brilliant for all sorts of patterns: pulling out email domains, reformatting phone numbers, turning "SMITH, jane" into "Jane Smith". Check the results, as it occasionally guesses wrong on unusual entries.
Note: Flash Fill doesn't update if the original data changes. Run it again if you add rows.
Method 3: The TEXTSPLIT function
In Microsoft 365 and Excel for the web, TEXTSPLIT splits text with a formula — and updates automatically when the original changes.
=TEXTSPLIT(A2, " ")
The result spills into as many columns as needed. Copy the formula down for the other rows.
A few more examples:
• Split by comma and space: =TEXTSPLIT(A2, ", ")
• Split into rows instead of columns: =TEXTSPLIT(A2, , ",")
• Split into rows instead of columns: =TEXTSPLIT(A2, , ",")
Just want the first or last word? Use:
• =TEXTBEFORE(A2, " ") — everything before the first space (first name).
• =TEXTAFTER(A2, " ", -1) — everything after the last space (surname).
• =TEXTAFTER(A2, " ", -1) — everything after the last space (surname).
Which one should I use?
| Method | Best for |
|---|---|
| Text to Columns | A one-off clean-up, any version of Excel |
| Flash Fill | Quick jobs and odd patterns |
| TEXTSPLIT | Lists that keep changing (Microsoft 365) |
Putting text back together
To join columns back into one, use:
=A2&" "&B2
or =TEXTJOIN(" ", TRUE, A2:C2) to join a range and skip blanks.
Troubleshooting
Leading zeros disappeared (e.g. 01234 became 1234). In step 3 of Text to Columns, set that column's format to Text.
Dates turned into strange numbers. Set the column format to Date in step 3, and choose the right order (DMY for UK dates).
#SPILL! with TEXTSPLIT. The cells to the right aren't empty. Clear them and the result will appear.
Wrapping up
For a quick one-off, Ctrl + E (Flash Fill) is hard to beat. For bigger lists, Data > Text to Columns — just make room first. On Microsoft 365, TEXTSPLIT keeps everything up to date automatically.
