TEXTSPLIT Function in Excel
TEXTSPLIT is the modern way to break a string into pieces by a delimiter. It replaces the old Text-to-Columns feature and the complicated FIND-LEFT-MID choreography — one function, spilling directly into cells, working with dynamic data. Excel 365 only, but genuinely transformative.
What it does: Splits text into a spilled array using delimiters. Can split into columns (by col_delimiter), into rows (by row_delimiter), or both at once (producing a 2D array). Optionally skips empties, controls case sensitivity, and pads short results.
What TEXTSPLIT does
TEXTSPLIT takes a string and a delimiter, and returns an array of the pieces between delimiters. =TEXTSPLIT("A,B,C", ",") spills into three horizontal cells containing "A", "B", "C". Instead of typing multiple LEFT/MID/RIGHT formulas or running Text-to-Columns manually, you write one formula.
Because it uses dynamic arrays, the output automatically grows or shrinks as the input changes. Add another item to the source string and a new cell appears in the spill. Delete an item and the spill contracts.
col_delimiter) to split into columns — the result spills horizontally. Use the third argument (row_delimiter) to split into rows — the result spills vertically. Use both together to split a CSV-style block into a full 2D grid.
Syntax breakdown
text Required
The string to split. Usually a cell reference. Can also be a literal string in quotes or the result of another function.
col_delimiter Required (unless row_delimiter provided)
The character or string that separates values within a row. Common: ",", " ", "|", ";". Can be multi-character. Pass an array like {",";";"} to split on either character.
row_delimiter Optional
The character that separates rows. Use CHAR(10) for line breaks. If both delimiters are given, TEXTSPLIT produces a 2D array — perfect for parsing pasted CSV data.
ignore_empty Optional
TRUE skips empty pieces (like "A,,B" only returns "A" and "B"). FALSE (default) keeps them as empty cells in the output.
match_mode Optional
0 (default) case-sensitive delimiter match; 1 case-insensitive. Only matters when your delimiter contains letters.
pad_with Optional
Value to use for "missing" cells in a 2D split when rows have different numbers of columns. Default is #N/A. Pass an empty string or 0 for cleaner output.
5 real-world examples
Example 1: Split a comma-separated list into columns
Cell A2 contains "red, green, blue, yellow":
Result: Four cells spilling horizontally: red | green | blue | yellow. Add another color to A2 and a fifth cell appears automatically.
Example 2: Split a full name into first and last
Cell A2 contains "Alice Chen":
Result: Two cells: Alice | Chen. Vastly simpler than the LEFT + FIND pattern. Handles any number of spaces gracefully.
Example 3: Parse a full CSV row into a 2D grid
Cell A2 contains a whole CSV block with rows and columns:
Result: A full 2D spilled array — each CSV row on its own row of the spreadsheet, each comma-separated value in its own column. Excel's most powerful text parser in one formula.
Example 4: Split with multiple possible delimiters
Text uses inconsistent delimiters — commas, semicolons, or spaces:
=TEXTSPLIT(A2, {",", ";", " "})Result: Splits on any of the three characters. Very useful for cleaning inconsistent data pasted from different sources.
Example 5: Get a specific field from a delimited string
Get the third field from a pipe-delimited string:
=INDEX(TEXTSPLIT(A2, "|"), 3)Result: The third piece of the split. Wrap TEXTSPLIT in INDEX to pull a specific position without spilling everything. Efficient for lookups on encoded strings.
Common errors and how to fix them
TEXTSPLIT doesn't exist in your Excel version. Only available in Excel 365 (updated versions from 2022 onward) and Excel Online. Not in Excel 2021, 2019, 2016, or on Mac Excel before 365. Fallback: use Text-to-Columns feature, or LEFT/MID/FIND chains.
The result would spill into cells that already contain data. Fix: clear the destination cells, or move the TEXTSPLIT formula to an area of the sheet with space. See the #SPILL! error guide.
Double delimiters in the source create empty cells. Set the fourth argument ignore_empty to TRUE: =TEXTSPLIT(A2, ",", , TRUE).
Rows have different column counts, so some cells are "missing." Add the pad_with argument: =TEXTSPLIT(A2, ",", CHAR(10), , , ""). Uses empty strings instead of #N/A.
TEXTSPLIT vs alternatives
| Approach | Best for | Watch out for |
|---|---|---|
| TEXTSPLIT | Dynamic, formula-based splitting | Excel 365 only |
| Text-to-Columns | One-time bulk operations on ranges | Not dynamic; overwrites source columns |
| LEFT + FIND + MID | Older Excel versions; specific positions | Verbose, error-prone with many delimiters |
| TEXTBEFORE / TEXTAFTER | One piece before or after a delimiter | Excel 365 only; one delimiter position at a time |
| Power Query | Complex, repeatable data transformations | Batch process, not formula; separate refresh cycle |
Version compatibility
Google Sheets has a similar function called SPLIT with different argument order — same concept, different syntax.
Download the practice workbook
Every example above, plus a full CSV-parsing template and multi-delimiter cleaning patterns.
Related functions
Frequently asked questions
Why doesn't TEXTSPLIT work in my Excel 2021?
Because TEXTSPLIT was added later, in Excel 365 updates during 2022. Excel 2021 has other dynamic arrays (FILTER, SORT, UNIQUE) but not TEXTSPLIT. Options: upgrade to Excel 365, use Text-to-Columns for one-time splits, or use LEFT + FIND chains for formulas.
Can TEXTSPLIT handle CSV with quoted fields?
Not gracefully. A CSV row like "Smith, John",42,USA should treat the comma inside quotes as data, not a delimiter. TEXTSPLIT treats every comma equally. For proper CSV parsing, use Power Query, which understands quoted-field escaping.
How do I split on line breaks?
Use CHAR(10) as the delimiter: =TEXTSPLIT(A2, , CHAR(10)). On Windows-formatted text with CR+LF line endings, use CHAR(13) & CHAR(10), or pre-clean with SUBSTITUTE.
What's the difference between TEXTSPLIT and Text-to-Columns?
TEXTSPLIT is a formula — the result updates automatically when the source changes. Text-to-Columns is a one-time operation that overwrites the source cells. Use TEXTSPLIT when you need live parsing; use Text-to-Columns for one-off cleanup of static data.
How do I get just one field from the split without spilling?
Wrap in INDEX: =INDEX(TEXTSPLIT(A2, ","), 3) gets the third field. Or use TEXTBEFORE / TEXTAFTER, which return just one piece without an array. For "everything before the second comma" specifically, TEXTBEFORE has an occurrence parameter: =TEXTBEFORE(A2, ",", 2).
Can I use TEXTSPLIT with multiple different delimiters at once?
Yes — pass an array constant: =TEXTSPLIT(A2, {",", ";", "|"}). Splits on any of the listed characters. Very useful for cleaning inconsistent data.
Is TEXTSPLIT case-sensitive?
By default yes, but only if your delimiter contains letters. Set the fifth argument match_mode to 1 for case-insensitive: =TEXTSPLIT(A2, "and", , , 1) would split on "and", "And", or "AND".
Parse any structured text — with the Sheets & Cells AI Add-in
Point the Add-in at any messy delimited data and it writes the TEXTSPLIT formula with the right delimiters, empty-handling, and padding — plus a fallback for pre-365 Excel versions. All inside Excel.
Learn about the Add-in →