TL;DR —
TEXTSPLITis the formula version of Text to Columns, with one huge difference: the result is a live array that re-splits whenever the source changes. Syntax:=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]). The second argument splits across into columns; the third splits down into rows — give both and one string becomes a grid. Two things trip people up: the result spills, so a blocked range throws#SPILL!, and to split on several delimiters you must pass an array constant{",", ";"}, not the string",;". Excel 365 and 2021+.
=TEXTSPLIT(A2, ",")
=TEXTSPLIT(A2, ",", ";") ' columns by comma, rows by semicolon
For two decades, breaking "Jones, Sarah, Finance" into separate cells meant the
Text to Columns wizard — a one-time, destructive paste that you had to re-run
by hand every time the data changed — or a nest of LEFT, MID, FIND that
nobody could read a month later. TEXTSPLIT does it in one formula, and because
it's a formula, the split stays live.
What you'll learn
- How
col_delimiterandrow_delimiterturn one string into a 2-D grid - Why multiple delimiters need an array constant
{",", ";"}, not",;" - What causes
#SPILL!and how to clear it pad_with— the argument that stops ragged rows throwing#N/A- When
TEXTSPLITretires yourLEFT/MID/FINDstack and Text to Columns
The mental model: Text to Columns, but live
Text to Columns is an action — you run it, it writes values once, and it's done;
change the source and the output is stale. TEXTSPLIT is a formula — it
describes the split and recomputes it forever. So instead of "select range, open
wizard, pick delimiter, click Finish, repeat next week," you type one expression
and the cells to its right (and below) fill themselves and stay correct:
' "Jones,Sarah,Finance" in A2 spills across three cells:
=TEXTSPLIT(A2, ",") ' -> Jones | Sarah | Finance
The output isn't typed into those cells — it spills into them from the formula
cell, exactly like FILTER and the other
dynamic-array functions.
The rule that unlocks everything: column vs row, and the delimiter array
TEXTSPLIT has two delimiter slots, and which one you use decides the shape of
the output. col_delimiter (2nd arg) splits the text sideways into columns;
row_delimiter (3rd arg) splits it downward into rows. Give both and a single
packed string becomes a rectangular grid:
' "a,b;c,d" -> a 2x2 grid: commas make columns, semicolons make rows
=TEXTSPLIT(A2, ",", ";")
And the trap that wastes the most time: to split on more than one delimiter,
you pass an array constant, not a longer string. ",;" is read as the single
two-character delimiter "comma-then-semicolon" — which never matches — so the
whole string lands in one cell. The right way:
=TEXTSPLIT(A2, {",", ";", " "}) ' split on comma OR semicolon OR space
=TEXTSPLIT(A2, ",;") ' WRONG: looks for the literal text ",;"
#SPILL! is about the neighbours, not the formula
Because the result expands to however many pieces the text has, TEXTSPLIT needs
empty cells to spill into. If a value, a label, or a merged cell sits in the
range the result wants, Excel can't lay it down and returns #SPILL!. The formula
is fine; the destination is blocked. Click the cell to see the highlighted
spill range, clear what's in it, and the split appears. Practical habit: put a
TEXTSPLIT where it has room to grow to the right and down — not next to a column
of existing data.
pad_with: the fix for ragged rows
When you split many rows that don't all have the same number of pieces — say one
address has a suite line and another doesn't — the short rows come back padded
with #N/A. The last argument, pad_with, replaces those with whatever you
choose:
=TEXTSPLIT(A2, ",", ";", , , "") ' pad missing cells with "" instead of #N/A
The two skipped arguments are ignore_empty (collapse consecutive delimiters)
and match_mode (set to 1 for case-insensitive matching). You rarely touch
them, but knowing the slot order keeps the commas straight.
The judgment call: when TEXTSPLIT replaces the old tools
If you're nesting LEFT/MID/RIGHT with FIND just to chop a string at its
delimiters, stop — that pattern is fragile and TEXTSPLIT does it in one go and
stays live. If you're reaching for Data ▸ Text to Columns on data that keeps
changing, you're signing up to re-run it forever; a TEXTSPLIT formula updates
itself. The honest exception is the same as for every dynamic-array function: for
a one-time import of hundreds of thousands of rows, Power Query is leaner than a
sheet full of live formulas. For everything that needs to stay current,
TEXTSPLIT wins. (Splitting is the inverse of joining — see
TEXTJOIN.)
How ExcelMaster helps
Real splits are rarely clean: break the full name into first and last, but only
on the first space; keep the rest of the address together. ExcelMaster
writes the TEXTSPLIT — with the right delimiter array, pad_with, and a clear
spill location — from a plain-English description, and tells you if the
destination range will collide. You describe the shape you want; it handles the
delimiter mechanics.
Frequently asked questions
How do I split text into columns with a formula?
Use =TEXTSPLIT(A2, ",") to split across into columns by comma. The result
spills into the cells to the right of the formula and updates whenever A2
changes — no wizard, no re-running.
How do I use multiple delimiters in TEXTSPLIT?
Pass them as an array constant in braces: =TEXTSPLIT(A2, {",", ";", " "}).
Writing ",; " as a plain string fails, because Excel treats it as one
multi-character delimiter rather than three separate ones.
Why does TEXTSPLIT show #SPILL!?
Something is blocking the cells the result needs to expand into — a value, a label, or a merged cell. Click the formula cell to see the spill range, clear whatever sits in it, and the result appears.
TEXTSPLIT vs Text to Columns — which should I use?
TEXTSPLIT is a live formula that re-splits when the source changes; Text to
Columns is a one-time action that writes static values you must re-run by hand.
For data that changes, use TEXTSPLIT. For a single bulk import, Text to Columns
or Power Query is fine.
Does TEXTSPLIT work in Excel 2016 or 2019?
No. TEXTSPLIT requires Excel 365 or Excel 2021+. On older versions, use Text to
Columns or a LEFT/MID/FIND formula.
Tested in
Tested in: Excel 365 (Windows 11) — last verified 2026-06-17.
Related guides: Excel TEXTJOIN · Excel CONCAT · Excel FILTER · VBA Split