How to Use the TEXTSPLIT Function in Excel: A Step‑by‑Step Guide
1. Understanding TEXTSPLIT: Purpose and Syntax
TEXTSPLIT is a dynamic‑array function introduced in Excel 365 (and Excel 2021). It takes a single text string and returns an array of substrings, splitting the string wherever one or more specified delimiters occur. Because the result spills automatically, you no longer need helper columns or nested MID/FIND formulas to break apart CSV‑style data.
Basic syntax
TEXTSPLIT(text, col_delimiter, [row_delimiter],
[ignore_empty], [match_mode], [pad_with])
| Argument | Required? | Description |
|---|---|---|
| text | Yes | The string to split. |
| col_delimiter | Yes | Delimiter(s) that separate columns. Can be a single character, a string, or an array of delimiters. |
| row_delimiter | No | Delimiter(s) that separate rows. If omitted, the function returns a single row. |
| ignore_empty | No | FALSE (default) keeps empty substrings as blank cells; TRUE skips them. |
| match_mode | No | 0 (default) – literal match; 1 – case‑insensitive; 2 – wildcard; 3 – regular‑expression (Excel 365 only). |
| pad_with | No | Value placed in cells where a split would otherwise produce a missing element (useful when rows have different column counts). |
Simple example
| A |
|---|
| John,Jane,Joe |
=TEXTSPLIT(A1, ",")
Result (spills into B1:D1)
| B | C | D |
|---|---|---|
| John | Jane | Joe |
Splitting into rows and columns
If the source contains both row and column delimiters (e.g., a list separated by commas and line breaks), supply both delimiters:
| A |
|---|
| Apple;Banana|Cherry |
=TEXTSPLIT(A2, ";", "|")
Result (spills into B2:C3 when A2 holds Apple;Banana|Cherry)
| B | C |
|---|---|
| Apple | Banana |
| Cherry | #N/A |
2. When to Use TEXTSPLIT vs. Other Text Functions
TEXTSPLIT is versatile, but it isn’t a universal replacement for every text‑handling need. Below is a quick reference to help you decide which tool is best for a given scenario.
| Scenario | Recommended function(s) | Why prefer it over TEXTSPLIT? |
|---|---|---|
| Simple single‑delimiter split (e.g., “John,Paul,George”) | TEXTSPLIT | One formula returns the full array, no helper functions needed. |
| Split on a regular‑expression pattern (e.g., any punctuation) | REGEXEXTRACT, REGEXREPLACE (Sheets) or FILTERXML (Excel) |
TEXTSPLIT only accepts literal text delimiters; regex functions handle complex patterns. |
| Extract only the first token (e.g., first name) | TEXTBEFORE (Excel 365) or LEFT + FIND |
Returns a single value without creating an array, which is more efficient. |
| Split into a fixed number of parts (e.g., first three columns) | TEXTSPLIT + INDEX |
TEXTSPLIT creates the full array; INDEX then picks the required columns. |
| Pull just the text before a delimiter | TEXTBEFORE |
Returns one value instead of an array. |
| Legacy compatibility (Excel 2019 or earlier) | MID, LEFT, RIGHT combined with FIND/SEARCH |
TEXTSPLIT is unavailable in older versions. |
3. Step‑by‑Step Example: Splitting Full Names
Suppose column A contains full names and you want the first and last names in columns B and C.
| A (Full Name) |
|---|
| John Doe |
| Jane Smith |
| Carlos Méndez |
3.1 Basic split
In B2 enter:
=TEXTSPLIT(A2, " ", , TRUE)
" "– space delimiter.- The third argument (row delimiter) is omitted, so the result spills horizontally (across columns).
TRUEtells Excel to ignore empty tokens (not needed here but harmless).
Drag the fill handle down to B4. Each row's spill fills columns B and C (first and last name).
3.2 Handling middle names
If a name contains a middle name, TEXTSPLIT will produce three columns. To keep the first and the last word, use INDEX:
=INDEX(TEXTSPLIT(A2, " ", , TRUE), 1, 1) // first name
=INDEX(TEXTSPLIT(A2, " ", , TRUE), 1, COUNTA(TEXTSPLIT(A2, " ", , TRUE))) // last name
Both formulas can be placed in B2 and C2 respectively and copied down.
4. Advanced Usage: Multiple Delimiters and Ignoring Empty Cells
TEXTSPLIT can accept an array of delimiters, which is ideal when data mixes commas, semicolons, spaces, etc.
Example data
| A |
|---|
| Apple, Banana;; Cherry , Date |
Formula
=TEXTSPLIT(A2, {";",","," "}, , TRUE)
{";",","," "}– array of delimiters (semicolon, comma, space).- The fourth argument (
TRUE) tells Excel to ignore empty cells, so the double semicolon (;;) does not create a blank column.
Result (spills into B2:E2)
| B | C | D | E |
|---|---|---|---|
| Apple | Banana | Cherry | Date |
Tips for reliable multi‑delimiter splits
| Tip | Reason |
|---|---|
Wrap delimiters in an array ({";",","," "}) |
Handles any number of characters without nesting multiple TEXTSPLIT calls. |
Set ignore_empty to TRUE |
Prevents stray blanks when delimiters appear consecutively or at the start/end of the string. |
Use TRIM on the source if leading/trailing spaces exist |
Otherwise spaces are treated as delimiters and may generate unwanted empty cells. |
5. Troubleshooting Common Errors
| Error / Symptom | Likely Cause | Quick Fix |
|---|---|---|
| #VALUE! | Empty delimiter string ("") or invalid argument. |
Supply a real delimiter such as "," or {";",","}. |
| #SPILL! (red triangle) | Destination range is blocked or contains merged cells. | Clear the obstructing cells or un‑merge the target area. |
| #N/A | Rows have different numbers of pieces. | Set pad_with (6th argument), e.g. "". |
| Extra blank rows/columns | Consecutive delimiters generate empty strings. | Set ignore_empty to TRUE or wrap the result with FILTER(...,LEN(...)>0). |
| #NAME? | Workbook opened in a version prior to Excel 2021/365. | Upgrade Excel or revert to legacy formulas (MID, FIND, etc.). |
Key pitfall: Excel’s TEXTSPLIT does not accept regular‑expression patterns, only literal delimiters.
6. Combining TEXTSPLIT with Other Functions
Once a string is split, you can pipe the resulting array directly into other dynamic‑array functions for cleaning, counting, or sorting.
| Goal | Sample data | Formula (uses TEXTSPLIT) | Result |
|---|---|---|---|
| Trim whitespace and remove blanks | A2: " Apple, , Banana , Cherry " |
=FILTER(TRIM(TEXTSPLIT(A2,",")),LEN(TRIM(TEXTSPLIT(A2,",")))>0) |
Apple Banana Cherry |
| Count distinct items | A3: "red|blue|red|green|blue" |
=COUNTA(UNIQUE(TEXTSPLIT(A3,"|"))) |
3 (red, blue, green) |
| Create a sorted list | A4: "zeta,alpha,gamma,beta" |
=SORT(TEXTSPLIT(A4,",")) |
alpha beta gamma zeta |
All formulas assume Excel 365 or Excel 2021.
7. FAQ
What versions of Excel support TEXTSPLIT?
TEXTSPLIT is available only in Excel 365 and Excel 2021 (or later). Earlier desktop versions (2019, 2016, etc.) do not recognize the function.
How does TEXTSPLIT differ from the older Text to Columns feature?
Text to Columns is a one‑time wizard that writes results into static cells and cannot be refreshed automatically. TEXTSPLIT is a formula that spills a dynamic array, updating instantly when the source text changes and requiring no manual re‑run.
Can TEXTSPLIT split text into columns instead of rows?
Yes. Supplying a column delimiter (the second argument) splits the string into columns, while a row delimiter (the third argument) creates new rows. Omit the row delimiter to get a single‑row spill, or omit the column delimiter to get a single‑column spill.
What happens if the delimiter is not found in the text?
TEXTSPLIT simply returns the original text in a single cell, with no error.
Is there an equivalent function in Google Sheets?
Google Sheets offers SPLIT, which works similarly but only accepts a plain‑text delimiter (no regex). For regex‑based splitting, Sheets provides REGEXEXTRACT, REGEXREPLACE, and REGEXMATCH. SPLIT does not have the full set of optional arguments that TEXTSPLIT provides (e.g., ignore‑empty, match‑mode, pad‑with).
With these basics, you can start replacing cumbersome legacy formulas with a single, readable TEXTSPLIT call and combine it with Excel’s powerful dynamic‑array ecosystem.