TEXTSPLIT breaks one cell into many — spilling across columns (and/or down rows) by a delimiter. It’s the formula version of Text to Columns, but live and automatic.
The example
A CSV-style cell split into columns.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Input | 1 | 2 | 3 |
| 2 | Ann,Sales,East | Ann | Sales | East |
The formula
Spill the pieces across columns:
How it works
TEXTSPLIT separates on a delimiter and spills:
- The 2nd argument is the column delimiter — the result spills across columns.
- A 3rd argument adds a row delimiter, splitting into a 2-D grid (rows and columns at once).
- Pass an array of delimiters
{",",";"}to split on several characters. - Set pad_with to fill ragged rows, or ignore_empty to skip blank pieces between consecutive delimiters.
Live, unlike Text to Columns. TEXTSPLIT recalculates when the source changes — Text to Columns is a one-time paste. For splitting down into rows instead, use the row-delimiter argument or see the split-to-rows recipe.
Try it: interactive demo
Text + delimiter → columns.
Variations
Split into a grid
Columns and rows:
Multiple delimiters
Array of separators:
Ignore empties
Skip blanks:
Pitfalls & errors
365 only. Older Excel uses Text to Columns or MID/FIND.
Spill needs room. The result spills into neighboring cells — if they’re occupied you get #SPILL!.
Consecutive delimiters. Two delimiters in a row create a blank cell unless you set ignore_empty.
Practice workbook
Frequently asked questions
How do I split text into columns with a formula in Excel?
How do I split into both rows and columns?
Why do I get a #SPILL! error?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class