Text
TEXTSPLIT function playground
Splits text into columns, and optionally rows, at every delimiter. The pieces spill into the cells around it.
The playground runs in your browser. Nothing you type is sent anywhere.
How TEXTSPLIT works
TEXTSPLIT cuts the text wherever col_delimiter appears and spills each piece into its own column. Add a row_delimiter and the text is cut into rows as well, giving a small table.
To split on several delimiters at once, give an array constant such as {",",";"}. Set ignore_empty to TRUE to drop the empty pieces you get from doubled delimiters.
It's the formula version of Data > Text to Columns: change the source cell and the result updates.
Syntax
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])| Argument | What it means |
|---|---|
textrequired | The text to split. |
col_delimiterrequired | Where to split into columns, such as ", ". Use {",",";"} for several. |
row_delimiteroptional | Where to split into rows. Leave empty to split into columns only. |
ignore_emptyoptional | TRUE skips empty pieces. FALSE (default) keeps them as blank cells. |
match_modeoptional | 0 case-sensitive (default), 1 ignore case. |
pad_withoptional | What to put in missing cells when rows have different lengths. Default #N/A. |
Examples
| Formula | What it does | Result |
|---|---|---|
=TEXTSPLIT(A2,", ") | Split "Kim, Grace" into last and first name. | Kim (spills 1×2) |
=TEXTSPLIT(C2,"-") | Break an order code into its four parts. | US (spills 1×4) |
=TEXTSPLIT(D2,";") | One tag per column. | red (spills 1×3) |
=TEXTSPLIT(D3,",",,TRUE) | ignore_empty drops the blank between the doubled commas. | blue (spills 1×2) |
=TEXTSPLIT(D5,",",";",,,"-") | Columns at commas, rows at semicolons; missing cells show -. | red (spills 2×2) |
Common mistakes
#SPILL!The pieces need empty cells to the right (and below). Clear anything in the way.
Leftover spacesSplitting "a, b" on "," leaves " b" with a space. Include the space in the delimiter or wrap the result in TRIM.
VersionTEXTSPLIT needs Microsoft 365, Excel 2024 or Excel for the web.
Related functions
Learn more
Download the example workbook (.xlsx)
The workbook has the same sample data and formulas, plus a practice sheet.