TEXTSPLIT - Split delimited text into cells
Separate imported fields across columns or down rows, with explicit empty-field and padding rules for a spilled array.
By the end, you can
- Split a record using a column delimiter.
- Choose empty-field and row-splitting behavior.
- Recognize spill space, padding, and version requirements.
Before you start: Understand text delimiters and an output range that must remain empty for a spilled array.
In this lesson
One string, several returned cells
TEXTSPLIT separates text at known delimiters and returns an array. An import line can become several fields without cutting the source apart manually. A column delimiter creates columns; a row delimiter creates rows. It splits where you tell it to split, not where a name or address semantically changes.
Syntax and arguments
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])For a row-only split, leave the column-delimiter position empty. Optional argument positions still need their separating commas.
| Argument | Purpose | Example |
|---|---|---|
textRequired | The source text to split. | A2 |
col_delimiterOptional | Separator for columns; may be omitted for a row-only split. | "|" |
row_delimiterOptional | Separator that starts another output row. | ";" |
ignore_emptyOptional | TRUE skips empty fields from consecutive delimiters; default FALSE keeps them. | TRUE |
match_modeOptional | 0 case-sensitive delimiter matching (default); 1 case-insensitive. | 1 |
pad_withOptional | Padding for unequal row lengths; default is #N/A. | "" |
A pipe-separated import record
Anika| A | B | C | D | |
|---|---|---|---|---|
| 1 | Import line | First field | Second field | Third field |
| 2 | Anika|Meera|Dev | Anika | Meera | Dev |
| 3 | DEL|Sales|Active | DEL | Sales | Active |
=TEXTSPLIT(A2,"|")Return three text values spilling across B2:D2: Anika, Meera, and Dev. Use the corresponding A3 formula for the second record.
- Keep the source in A2 and clear B2:D2 before entering the formula.
- Enter =TEXTSPLIT(A2,"|") in B2 only.
- Excel places the three results across the available cells.
- For another source row, enter the formula at B3; do not type separate formulas into the cells occupied by each spill.
Empty fields, vertical output, and padding
=TEXTSPLIT("PEN||INK","|")Two adjacent pipes create an empty middle field between PEN and INK.
=TEXTSPLIT("PEN||INK","|",,TRUE)Leave row_delimiter omitted and set ignore_empty to TRUE, producing two columns.
=TEXTSPLIT("Delhi;Pune;Mumbai",,";")Omit col_delimiter and use a semicolon as row_delimiter; return one city per row.
=TEXTSPLIT("A,B;C",",",";",FALSE,0,"")Commas create columns and the semicolon creates a row. The second row's absent second item is padded with the explicitly supplied empty text.
=TEXTSPLIT("DEL|Sales;Active",{"|",";"})In Excel, an array constant supplies both accepted separators. Return the three fields as columns.
Spill behavior and compatibility
- A blocked result area produces #SPILL!. Put the formula in ordinary worksheet cells with room to expand, rather than inside an Excel Table.
- No delimiter match returns the original text as a one-item result. Splitting does not trim the returned fields or infer their types as business quantities.
- Delimiter matching defaults to case-sensitive; match_mode 1 can ignore case. For several delimiters, supply an array constant rather than a combined string that means something else.
- Rows with unequal field counts use #N/A padding unless pad_with is supplied. Preserving empty fields is often necessary to keep positional records aligned.
- TEXTSPLIT is available in Microsoft 365 and Excel 2024, not perpetual Excel 2021, 2019, or 2016. Dynamic-array support alone does not mean this particular function exists.
Common mistakes and where to run this lesson
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.