Excel TEXTSPLIT Function: Split Text Into Columns With a Formula
Splits text on a delimiter and spills the pieces across columns or down rows.
TEXTSPLIT does what Text to Columns does, except as a formula. Give it some text and a delimiter and it breaks the text apart, spilling each piece into its own cell. "North,Drill,Overdue" becomes three cells.
The advantage over Text to Columns is that it is live. Text to Columns is a one-time operation on a selection; run it, and you have to run it again next time the data arrives. TEXTSPLIT sits in a cell and re-splits automatically whenever the source text changes, which makes it the right tool for a sheet that receives a fresh export every week.
It can split in both directions at once. The first delimiter argument splits across columns, the second splits down rows — so a single formula can turn one cell containing a delimited block of text into a proper rectangular table.
Syntax
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])Arguments
textRequired- The text to split.
col_delimiterRequired- What to split on when spreading across columns. Pass several in an array constant to split on any of them.
row_delimiterOptional- What to split on when spreading down rows.
ignore_emptyOptional- TRUE skips empty results caused by two delimiters in a row. Defaults to FALSE.
match_modeOptional- 0 is case sensitive (the default), 1 is not.
pad_withOptional- What to fill missing cells with when rows split into different numbers of pieces. Defaults to #N/A.
The example data
One delimited string per row, in A2:A4.
| A | |
|---|---|
| 1 | Record |
| 2 | North,Drill,Overdue |
| 3 | South,Lead,Paid |
| 4 | North,Goggles,Overdue |
Worked examples
=TEXTSPLIT(A2, ",")Result: North | Drill | Overdue
Three pieces spilling across three columns.
=TEXTSPLIT(A2, , ",")Result: North, then Drill, then Overdue, stacked vertically
Skipping the column delimiter with an empty argument and passing a row delimiter instead splits downwards.
=TEXTSPLIT("Alice Moreau", " ")Result: Alice | Moreau
Splitting a full name on a space — the everyday use, and one line rather than LEFT/FIND gymnastics.
=TEXTSPLIT(A2, {",", ";"})Result: North | Drill | Overdue
An array constant splits on either delimiter, which handles exports that are inconsistent about separators.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#SPILL!
Why it happens: The cells the pieces need are not empty.
How to fix it: Clear the row to the right of the formula, or the column below it if you are splitting into rows.
#N/A in some cells
Why it happens: Splitting several rows at once where they do not all have the same number of pieces. The short rows are padded.
How to fix it: Set the pad_with argument: =TEXTSPLIT(A2:A4, ",", , , , "").
Empty columns appear
Why it happens: Two delimiters next to each other in the source produce an empty piece.
How to fix it: Pass TRUE for ignore_empty to skip them.
#NAME?
Why it happens: TEXTSPLIT is newer than the rest of the dynamic array family — it did not ship until 2022.
How to fix it: It needs current Microsoft 365. Excel 2021 has FILTER and UNIQUE but not TEXTSPLIT; there, use Text to Columns or LEFT/MID/FIND.
Tips worth knowing
- TEXTBEFORE and TEXTAFTER are the better choice when you only want one piece rather than all of them.
- Split on a line break with CHAR(10) as the delimiter to unpack a multi-line cell.
- Wrap in TRIM to clean up spaces around the pieces: =TRIM(TEXTSPLIT(A2, ",")).
- Give it a whole column and it splits every row at once, producing a full table from a single formula.
Frequently asked questions
How is TEXTSPLIT different from Text to Columns?
Text to Columns is a one-off action that overwrites cells and has to be repeated whenever new data arrives. TEXTSPLIT is a formula that recalculates automatically and leaves the source intact. For a weekly import, TEXTSPLIT means setting it up once instead of every week.
Can TEXTSPLIT split on more than one delimiter?
Yes. Pass an array constant as the delimiter: =TEXTSPLIT(A2, {",", ";", "|"}) splits on any of the three, which is useful for messy exports that are not consistent.
How do I split a full name into first and last?
=TEXTSPLIT(A2, " ") gives both parts in adjacent cells. For just one of them, TEXTBEFORE(A2, " ") and TEXTAFTER(A2, " ") are clearer, and TEXTAFTER handles middle names better if you pass -1 as the instance number to take the last space.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.