A cell contains "Alice, Bob; Carol". How do you split it into three separate values? Excel 365’s TEXTSPLIT does this in one formula. It splits a text string by one or more delimiters into a spilled array. The result can span columns, rows, or both simultaneously. Before TEXTSPLIT, the only options were Text to Columns (destructive and manual) or a complex MID, FIND, and LEN chain. TEXTSPLIT is fully dynamic, updates when the source changes, and handles multiple delimiters in a single call.
This guide covers all six TEXTSPLIT arguments, with eight practical examples. You will learn how to split by multiple delimiters, split into rows, ignore blank tokens, and parse Key=Value strings. You will also combine TEXTSPLIT with TRIM and ARRAYTOTEXT. Each example also compares TEXTSPLIT to the Text to Columns wizard so you see exactly what changes.
What Does TEXTSPLIT Do?
TEXTSPLIT divides a text string into parts using one or more delimiter characters. By default, it splits into columns — each token becomes a separate column in the same row. Specifying a row delimiter instead (or in addition) splits into rows or creates a two-dimensional array. The result spills into as many cells as needed automatically.
The key advantages over Text to Columns are dynamic updating and multi-delimiter support. Specifically, these two capabilities cover most real-world text-splitting scenarios. Text to Columns is destructive — it overwrites adjacent cells and does not update when the source changes. TEXTSPLIT, by contrast, recalculates whenever the source cell changes. Additionally, you can pass multiple delimiters as an array. This handles messy real-world data where commas, semicolons, and spaces appear mixed.
What Is the TEXTSPLIT Syntax?
| Argument | Required? | Description |
|---|---|---|
| text | Required | The text string to split. A cell reference, text value, or formula result that returns text. |
| col_delimiter | Required | The character(s) that separate columns. Can be a single string, an array of strings {"," , ";"}, or empty string "" (splits every character). Omit (use "") to split only by row_delimiter. |
| row_delimiter | Optional | The character(s) that start a new row. Used to create 2D split results. Omit for column-only splitting. |
| ignore_empty | Optional | FALSE (default) = keep blank tokens from adjacent delimiters. TRUE = discard empty tokens (useful for messy data with repeated delimiters). |
| match_mode | Optional | 0 (default) = case-sensitive matching. 1 = case-insensitive matching. |
| pad_with | Optional | Value to fill empty positions in a 2D result where rows have different lengths. Default is #N/A. Use "" for blank. |
Examples 1–8: TEXTSPLIT in Practice
The most common use is splitting comma-separated values into separate columns. Pass the cell and the delimiter character. The result spills horizontally. Each token occupies one column in the same row. This replaces Text to Columns for any situation where the source data may change.
Real-world data is rarely consistent. A column might contain commas in some cells, semicolons in others, or both mixed. TEXTSPLIT handles this by passing multiple delimiters as an array. All listed delimiters are treated as split points in a single pass. This eliminates the need to run two separate SUBSTITUTE+TEXTSPLIT chains.
Specifying both a column delimiter and a row delimiter produces a two-dimensional result — a proper table. This is particularly useful for parsing structured text strings where rows are separated by one character (like a newline or semicolon) and columns by another (like a comma or pipe). TEXTSPLIT builds the full table in one formula.
Many APIs and log files return data in "Key=Value" format, where pairs are separated by a delimiter like "&" or ";". TEXTSPLIT with a 2D split parses these structures directly. Each row becomes one key-value pair. TRANSPOSE or CHOOSECOLS then separates the keys from the values without additional formulas.
Examples 5–8: Advanced TEXTSPLIT Patterns
Sometimes a whole CSV file is pasted into one cell. TEXTSPLIT can parse it directly. Use CHAR(10) as the row delimiter and comma as the column delimiter. The result is a full table that updates if the source cell changes. This is significantly faster than Text to Columns for recurring imports.
Counting tokens in a delimited string used to require counting delimiters with LEN and SUBSTITUTE. TEXTSPLIT makes it simpler: split the string and count the columns. COLUMNS(TEXTSPLIT(...)) returns the number of tokens. This is also useful for validating that a string has the expected number of parts.
TEXTSPLIT defaults to case-sensitive delimiter matching. Setting match_mode = 1 makes the split case-insensitive. This is useful for natural language data where a separator word like "AND" might appear as "and", "And", or "AND" in different records. Case-insensitive mode handles all variations in a single formula.
Combining TEXTSPLIT with ARRAYTOTEXT (or TEXTJOIN) enables a clean split-process-rejoin workflow. Split a messy string, apply SORT, TRIM, or FILTER to the tokens, then reassemble with TEXTJOIN or ARRAYTOTEXT. This pattern is useful for cleaning tag lists, normalising category strings, and sorting comma-separated values in a cell.
Common Issues and How to Fix Them
TEXTSPLIT returns #VALUE! or #N/A
#VALUE! usually means the delimiter was not found in the text, or the text argument is not a text string — a number passed without TEXT() conversion causes this. #N/A appears in the padded positions of uneven 2D results when pad_with is not specified. Always set pad_with to "" for clean display when using both row and column delimiters.
Tokens have leading or trailing spaces
TEXTSPLIT does not trim whitespace from tokens. A string "Alice, Bob" split by comma produces "Alice" and " Bob" (note the space before Bob). Wrap the result with TRIM: =TRIM(TEXTSPLIT(A2,",")). Alternatively, include the space in the delimiter: =TEXTSPLIT(A2,", ") treats comma-space as a single delimiter, avoiding the issue without a TRIM wrapper.
Frequently Asked Questions
-
What does TEXTSPLIT do in Excel?+TEXTSPLIT splits a text string into separate cells using one or more delimiters. By default, tokens go into separate columns in the same row. You can also split into rows or produce a 2D table by supplying both delimiter arguments. Multiple delimiters are supported as an array. The result spills automatically and updates when the source text changes. TEXTSPLIT is available in Excel 365 only.
-
How is TEXTSPLIT different from Text to Columns?+Text to Columns is a manual, one-time operation. It overwrites adjacent cells and does not update when data changes. TEXTSPLIT is a dynamic formula that recalculates automatically. Additionally, it supports multiple delimiters, row output, 2D table output, and combinations with SORT, TRIM, and FILTER. For data that changes regularly, TEXTSPLIT is almost always the better choice. Text to Columns remains useful for a quick one-off split where no formula maintenance is needed.
-
How do I split text into rows instead of columns?+Pass the delimiter as the third argument (row_delimiter) instead of the second (col_delimiter), and pass an empty string as the col_delimiter: =TEXTSPLIT(A2, "", ",") splits by comma into rows. Each token appears in a separate row below the formula cell. Alternatively, use TRANSPOSE on the column result: =TRANSPOSE(TEXTSPLIT(A2, ",")) also produces a vertical result from a column split.
-
How do I extract just one specific token from a TEXTSPLIT result?+Wrap TEXTSPLIT in INDEX and specify the column number: =INDEX(TEXTSPLIT(A2,","),1,3) returns the 3rd comma-separated token. The first argument of INDEX is the TEXTSPLIT array, the second is the row (use 1 for a single-row result), and the third is the column (token) number. For the last token, use COLUMNS(TEXTSPLIT(A2,",")) as the column number to always get the final item regardless of how many tokens the string contains.