TEXTSPLIT
Split text by a delimiter and spill the pieces across columns, rows, or both.
Spreadsheet editor
Spreadsheet editor
Syntax
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
Returns: Array Arguments
| Argument | Required | Description |
|---|---|---|
| text | Yes | The text you want to split. This can be typed in quotation marks or a reference to a cell that holds the text. |
| col_delimiter | Yes | The character or word that marks where to split the text across columns. To split on more than one delimiter, pass an array constant like {",",";"}. |
| row_delimiter | No | The character or word that marks where to split the text down rows. Use this together with col_delimiter to build a grid from one cell. |
| ignore_empty | No | Set to TRUE to skip empty values when two delimiters sit next to each other. Defaults to FALSE, which leaves an empty cell in the gap. |
| match_mode | No | Set to 0 (the default) for a case-sensitive match, or 1 to ignore case when matching the delimiter. |
| pad_with | No | The value used to fill empty spots when rows come out uneven. By default TEXTSPLIT pads with a #N/A error. |
About
TEXTSPLIT takes one piece of text and breaks it apart wherever a delimiter appears, then spills the results into separate cells. Point it at a value like a comma-separated list, tell it what to split on, and it fills the cells to the right (and down, if you want) with each chunk. It is the live, formula-driven answer to the old Text to Columns wizard, and it is the inverse of TEXTJOIN.
Use it when you need to pull a single string into structured columns: a full name into first and last, a CSV line into fields, or a tag list into a tidy grid. Set col_delimiter to spread pieces across columns and row_delimiter to push them down rows, so you can turn one messy cell into a small two-dimensional table in a single formula. Because the result spills automatically, you write the formula once and the whole array updates when the source text changes.
TEXTSPLIT works in Microsoft 365 and Excel 2021 and later. On older versions you can fall back to nesting LEFT, MID, and FIND, though that gets clumsy fast. To grab just one side of a delimiter instead of every piece, reach for TEXTBEFORE or TEXTAFTER, and clean up stray spaces with TRIM.
Use it when you need to pull a single string into structured columns: a full name into first and last, a CSV line into fields, or a tag list into a tidy grid. Set col_delimiter to spread pieces across columns and row_delimiter to push them down rows, so you can turn one messy cell into a small two-dimensional table in a single formula. Because the result spills automatically, you write the formula once and the whole array updates when the source text changes.
TEXTSPLIT works in Microsoft 365 and Excel 2021 and later. On older versions you can fall back to nesting LEFT, MID, and FIND, though that gets clumsy fast. To grab just one side of a delimiter instead of every piece, reach for TEXTBEFORE or TEXTAFTER, and clean up stray spaces with TRIM.
Examples
Drop empty pieces between delimiters
See how double commas leave blank cells in the default split. Set ignore_empty to TRUE and watch the gaps disappear, leaving just the real tags.
Spreadsheet editor
Split on more than one separator
Some values use dashes and others use slashes. Pass an array constant as the delimiter so one formula handles both. Try swapping a separator and the numbers still line up.
Spreadsheet editor
Replace #N/A padding in uneven grids
Watch what happens when one row is shorter than the others: the default grid fills the gap with #N/A. Add pad_with as "" to fill it with a blank instead.
Spreadsheet editor
Watch out for
⚠
#N/A padding in uneven results
When you split into both columns and rows and some rows are shorter, TEXTSPLIT fills the missing spots with #N/A by default.
→ Set the pad_with argument to a fallback value, like =TEXTSPLIT(A2, ",", ";", , , "") to fill gaps with a blank instead of an error.
⚠
Empty cells from double delimiters
Text like "a,,b" has two commas in a row, so TEXTSPLIT leaves an empty cell in the middle by default.
→ Set ignore_empty to TRUE to skip those gaps, or clean the source text first with SUBSTITUTE.
⚠
Spill range is blocked
TEXTSPLIT needs empty cells to fill. If anything sits in the way, the formula returns a #SPILL! error instead of the pieces.
→ Clear the cells to the right and below the formula so the result has room to spill.
Tips & notes
Pass an array constant like {","," "} as a delimiter to split on several characters at once. TEXTSPLIT spills its result, so leave room to the right and below the formula. It is the reverse of TEXTJOIN: one stitches values together, the other pulls them apart. When you only need a single piece rather than every chunk, TEXTBEFORE and TEXTAFTER are a better fit.
Common questions
How is TEXTSPLIT different from Text to Columns?
Text to Columns is a one-time wizard that pastes static values. TEXTSPLIT is a live formula, so the split updates on its own whenever the source text changes.
Can I split on more than one delimiter at once?
Yes. Pass an array constant for the delimiter, like =TEXTSPLIT(A2, {",",";"}), and TEXTSPLIT splits on every character you list.
Why does TEXTSPLIT return #N/A in some cells?
When you split into rows and columns and the rows are different lengths, the short rows get padded. Set the pad_with argument to a blank or other value to replace those #N/A results.
Can I use TEXTSPLIT in Excel 2019 or Google Sheets?
No. TEXTSPLIT needs Microsoft 365 or Excel 2021, and Google Sheets does not have it. In Google Sheets, the SPLIT function does a similar job.