How to Split a Cell Into Columns in Excel
Excel has four ways to split one cell of delimited text into multiple columns: Text to Columns for a quick one-time split, TEXTSPLIT for a formula that stays live, Flash Fill for pattern-based splits that don't follow a fixed delimiter, and Power Query for a repeatable step in a refreshable pipeline.
Quick answer: For a one-time split, select the column, go to Data → Text to Columns, choose Delimited, pick your separator, and finish. For a live formula that updates automatically, use =TEXTSPLIT(A2,",") in Excel 365. Text to Columns overwrites data to the right of the selected column, so insert blank columns first or the paste will clobber adjacent data.
How do I split a cell with Text to Columns?
Text to Columns is a one-time wizard: it reads the delimiter you specify, breaks the text into pieces, and writes those pieces into the columns to the right — permanently, as static values.
1. Select the column with combined text (e.g. "Smith, John")
2. Data tab → Text to Columns
3. Choose "Delimited" → Next
4. Check the delimiter (Comma, Space, Tab, or "Other")
5. Preview the split in the dialog → Next
6. Set the destination cell (or leave as-is) → Finish
Warning: Text to Columns overwrites any existing data
in the columns to the right of the split. Insert enough
blank columns first, or set a different destination cell.Because the result is static text, it does not update automatically — editing the original combined value later means running Text to Columns again.
How do I split a cell with the TEXTSPLIT formula?
TEXTSPLIT spills the pieces of one cell into adjacent cells as a live formula, so the result recalculates the moment the source text changes — the main advantage over Text to Columns.
=TEXTSPLIT(A2, ",")
' "Smith,John" spills across: Smith | John
=TEXTSPLIT(A2, ",", , TRUE)
' 4th argument ignore_empty=TRUE drops blank pieces,
' useful when the source has doubled delimiters ("a,,b")
=TEXTSPLIT(A2, ",", ";")
' col_delimiter "," AND row_delimiter ";" —
' splits into both columns and rows in one formulaTEXTSPLIT is Excel 365 only — it isn't available in Excel 2021, 2019, or earlier perpetual licenses. Because it spills, it also refuses to overwrite existing cells: you get #SPILL! instead of silently clobbering adjacent data, which is safer than Text to Columns in that respect.
How do I split a cell with Flash Fill?
Flash Fill learns a pattern from one or two examples you type manually and fills the rest of the column to match — useful when the split logic isn't a simple fixed delimiter.
Column A: "Smith, John (Sales)"
Column B: type "Smith" manually next to the first row
Column C: type "John" manually next to the first row
Press Ctrl+E in each column (or Data → Flash Fill)
Excel detects the pattern and fills the rest downFlash Fill shines when the split rule is irregular — pulling just a first name out of a full name with a title, or extracting a domain from an inconsistent email format — cases where a plain delimiter split would produce garbage. Like Text to Columns, its output is static text with no link back to the source. See Flash Fill examples and why Flash Fill sometimes fails if the pattern isn't being detected correctly.
How do I split a cell with Power Query?
Power Query's Split Column step works like Text to Columns but lives inside a query, so it re-runs automatically every time the underlying data refreshes — the right choice for a repeated import.
1. Select data → Data tab → From Table/Range
2. In Power Query Editor: right-click the column → Split Column
3. Choose "By Delimiter" (comma, space, custom) or
"By Number of Characters" (fixed-width splits)
4. Configure options → OK
5. Home → Close & Load
Re-running the query (Data → Refresh All) re-applies
the split to newly imported rows automatically.See Power Query basics for the broader Query Editor workflow this step lives inside.
Which method should I use?
| Situation | Best method |
|---|---|
| Quick one-time split, no version restrictions | Text to Columns |
| Need the result to stay live as source data changes | TEXTSPLIT (Excel 365 only) |
| Irregular pattern, not a fixed delimiter | Flash Fill |
| Recurring import that needs the same split every refresh | Power Query |
What are common mistakes when splitting cells?
- Not clearing destination columns first: Text to Columns overwrites data to the right without warning
- Splitting numbers that get reformatted: a split date fragment like "03" can turn into the number 3, losing the leading zero — set the destination column format to Text in the wizard
- Expecting TEXTSPLIT on Excel 2021 or earlier: it's 365 only; use Text to Columns or Power Query instead
- Re-running Text to Columns on already-split data: can mangle columns that no longer contain the original delimiter
Pro Tip: If you need the split values to feed formulas elsewhere in the workbook, prefer TEXTSPLIT over Text to Columns or Flash Fill — both of those produce static values that silently go stale when the source text is edited, while TEXTSPLIT recalculates automatically like any other formula.
← Back to Excel Tips