Home › Excel basics › Text to Columns & Flash Fill
How to Split Cells in Excel: Text to Columns, Flash Fill and TEXTSPLIT
When first and last names, addresses or codes are stuck together in one column, you don't need to retype them. Excel has three ways to split them: Text to Columns, Flash Fill and the TEXTSPLIT function.
Excel CSE · In this guide
Text to Columns
Best for data with a clear separator, like commas, semicolons or spaces.
- Make sure there are empty columns to the right; the split data overwrites whatever is there.
- Select the column to split.
- Go to Data › Text to Columns.
- Choose Delimited (split at a character) and click Next.
- Tick the separator: Tab, Semicolon, Comma, Space, or type your own under Other. The preview shows the result.
- Click Next, set a column format if needed (for example Text, to keep leading zeros in codes), choose a Destination, and click Finish.
| A | B | C | |
|---|---|---|---|
| 1 | Anna,Jansen,Amsterdam | ||
| 2 | ↓ Text to Columns, comma | ||
| 3 | Anna | Jansen | Amsterdam |
Choose Fixed width instead of Delimited when every part has the same number of characters, such as account numbers.
Bonus trick: Text to Columns with no changes (just click Finish) converts numbers and dates stored as text into real numbers and dates.
Flash Fill (Ctrl+E)
Flash Fill recognizes a pattern from an example you type. Great when there's no single separator.
- Next to "Anna Jansen" in A2, type Anna in B2.
- Go to the cell below (B3) and press Ctrl+E (or Data › Flash Fill).
- Excel fills in the first names for every row.
It also works for combining (type "Jansen, Anna"), reformatting phone numbers, or extracting the domain from email addresses. Check the results: if the pattern is ambiguous, give a second example. Flash Fill is static; it doesn't update when the source changes.
TEXTSPLIT: a formula that updates
In Microsoft 365 and Excel 2024:
=TEXTSPLIT(A2, ",")splits "Anna,Jansen,Amsterdam" into three cells and updates automatically when A2 changes. For more control, see TEXTBEFORE and TEXTAFTER or LEFT, RIGHT and MID.
Which method should you use?
| Situation | Best option |
|---|---|
| One-off split with a clear separator | Text to Columns |
| Irregular pattern, quick result | Flash Fill |
| Data that keeps changing | TEXTSPLIT or LEFT/MID/RIGHT formulas |
| Imported CSV in one column | Text to Columns with Comma or Semicolon |
Frequently asked questions
Why is Flash Fill not working?
It may be turned off: File › Options › Advanced › Automatically Flash Fill. Also make sure your example is directly next to the data, and try pressing Ctrl+E in the cell below your example.
Text to Columns removed the leading zeros in my codes. How do I keep them?
In the last step of the wizard, select the column and set its format to Text before clicking Finish.
Can I undo Text to Columns?
Yes, press Ctrl+Z straight after. Keep a copy of the original column if you're unsure.