The Excel TEXTSPLIT function splits one text value into many cells by a delimiter — like Text to Columns, except it’s a live formula that spills the pieces onto the sheet and updates automatically when the source changes. It can split into columns, into rows, or into both at once, and it retires whole families of LEFT/MID/FIND gymnastics. Its mirror image is TEXTJOIN, and for grabbing just one piece, TEXTBEFORE and TEXTAFTER are often simpler.
=TEXTSPLIT(A2, , ";").
Syntax
| Argument | Description | |
|---|---|---|
text | Required | The text to split — a string or a cell reference. |
col_delimiter | Required | The character(s) that mark where to split into columns. Can be more than one character (e.g. ", ") or an array of delimiters. |
row_delimiter | Optional | The character(s) that start a new row. Supply both delimiters to spill a full 2-D table from one cell. |
ignore_empty | Optional | FALSE (default) keeps empty cells for consecutive delimiters; TRUE drops them. |
match_mode | Optional | 0 (default) = case-sensitive delimiter matching · 1 = case-insensitive. |
pad_with | Optional | What to show in gaps when rows of a 2-D split have unequal lengths. Default is #N/A. |
Available in: Excel for Microsoft 365 and Excel for the web only. Excel 2021 and earlier show #NAME?. The result is a dynamic array that spills into neighboring cells.
Split text into columns
A system export crams item, quantity, and price into one pipe-delimited string. One formula per row breaks it apart — the formula in C2 spills across C2:E2:
| A | C | D | E | ||
|---|---|---|---|---|---|
| 1 | Raw export | Item | Qty | Price | |
| 2 | Olive Oil|12|4.99 | Olive Oil | 12 | 4.99 | |
| 3 | Sea Salt|40|2.25 | Sea Salt | 40 | 2.25 | |
| 4 | Basil Pesto|8|6.50 | Basil Pesto | 8 | 6.50 |
Copy it down and every row unpacks itself. Edit A2 and the pieces update instantly — something Text to Columns can’t do.
The delimiter can be several characters long, which is the clean way to avoid stray leading spaces:
Try it: interactive TEXTSPLIT demo
Edit the text, pick a delimiter, and watch the pieces spill. Note what ignore_empty does to the double comma.
Two-dimensional splits and power moves
Supply both delimiters and TEXTSPLIT unpacks an entire mini-table from a single cell — commas make columns, semicolons make rows:
Pass an array constant to split on several different delimiters in one go:
To grab just one piece, wrap the spill in INDEX — or skip TEXTSPLIT entirely and use TEXTBEFORE/TEXTAFTER:
Splitting at every space turns a sentence into words — count them with COUNTA:
Round trip: TEXTJOIN reverses a split — =TEXTJOIN("|", TRUE, TEXTSPLIT(A2, ",")) swaps commas for pipes in one move.
Errors & common pitfalls
#NAME? — your Excel doesn’t have it. TEXTSPLIT ships only in Excel for Microsoft 365 and Excel for the web. Excel 2021 and earlier don’t recognize the name — the old fallback is Text to Columns or FIND/MID formulas.
#SPILL! — no room for the pieces. The cells the result needs must be empty. Clear whatever is blocking the spill (click the warning icon to see the obstruction).
#N/A inside a 2-D result — uneven rows. When rows have different numbers of pieces, TEXTSPLIT pads the short ones with #N/A by default. Set pad_with to "" for clean blanks: =TEXTSPLIT(A2, ",", ";", , , "").
Pitfall: everything comes back as text. Split "Qty|12" and the 12 is a text string, not a number — SUM ignores it. Coerce with VALUE or a double negative: =--INDEX(TEXTSPLIT(A2,"|"),2).
Pitfall: delimiters are case-sensitive by default. Splitting on "x" won’t split at "X" unless you set match_mode to 1.
Practice workbook
Frequently asked questions
What's the difference between TEXTSPLIT and Text to Columns?
How do I split text by multiple different delimiters?
=TEXTSPLIT(A2, {",",";","|"}) splits wherever any of the three characters appears.How do I get just one piece instead of the whole spill?
=INDEX(TEXTSPLIT(A2,"-"), 2) returns the second piece only. For first-or-last-piece jobs, TEXTBEFORE and TEXTAFTER are usually shorter.Why do I see #N/A cells in my TEXTSPLIT result?
pad_with — pass "" as the sixth argument for blank-looking cells.Why does TEXTSPLIT return #NAME? in my Excel?
Are the split results numbers or text?
--) before doing math on them.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class