TEXTSPLIT function
TEXTSPLIT breaks text into separate cells across columns, rows or both, and returns the pieces as an array. It is a formula-based alternative to Text to Columns that updates when the source changes.
Example
Read it as a sentence: split a,b,c at each comma, then take the value in row 1, column 2, which gives b.
Syntax
TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
Arguments in [square brackets] are optional.
-
text(required) - The text to split.
-
col_delimiter(required) - Delimiter that separates columns.
-
row_delimiter(optional) - Delimiter that separates rows.
-
ignore_empty(optional) - TRUE skips empty pieces; default FALSE.
-
match_mode(optional) - 0 case-sensitive (default), 1 ignore case.
-
pad_with(optional) - Value for missing cells in uneven rows; default #N/A.
Returns: An array of the split pieces.
One TEXTSPLIT formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=TEXTSPLIT("a,b,c",",") | a | Into columns |
=TEXTSPLIT("a,b;c,d",",",";") | a | 2D |
=TEXTSPLIT("a,b,c",,",") | a | Into rows |
=TEXTSPLIT("a,,b",",") | a | Empty kept |
=TEXTSPLIT("a,,b",",",,TRUE) | a | Ignore empty |
=TEXTSPLIT("aXbxc","x",,,1) | a | Case-insensitive |
=TEXTSPLIT("a,b;c",",",";") | a | Uneven rows pad |
=TEXTSPLIT("abc","") | #VALUE! | Empty delimiter |
Watch out for
- Spills, so the cells beside and below must be empty.
- Consecutive delimiters create empty pieces unless ignore_empty is TRUE.
Where it works
- Excel
- TEXTSPLIT arrived in Excel 2024. Older versions do not have it. Excel 2024 and Microsoft 365.
- Google Sheets
- Done with a different function: `SPLIT`. SPLIT(text, delimiter) is similar, with different options.
Part of Text functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .