Practice free

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.

Practice this in the app

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.

FormulaExcel showsCase
=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 .