The TEXTSPLIT function in Excel
TEXTSPLIT cuts text into pieces wherever a delimiter (a separator such as a space, comma or hyphen) appears, and spills each piece into its own cell. It does the job of Text to Columns, but as a live formula that updates when the text changes. Use it to split full names, addresses, product codes and tag lists.
Syntax
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
| text | The text to split, usually a cell. |
|---|---|
| col_delimiter | Where to cut, in quotes, like " " or ", ". The pieces spill across columns. |
| row_delimiter | Optional. Where to start a new row, so pieces can spill down as well. |
| ignore_empty | Optional. TRUE skips empty pieces, such as the gap between two commas in a row. The default FALSE keeps them. |
| match_mode | Optional. 1 makes letter delimiters case-insensitive. The default 0 is case-sensitive. |
| pad_with | Optional. What to fill gaps with when rows have different numbers of pieces. The default is #N/A. |
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
Each order code holds a region, a year and an order number, separated by hyphens. In C2, use TEXTSPLIT to split the code in A2 at each hyphen. The three pieces will spill into C2:E2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order no. | ||||||
| 2 | |||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 |
Tip: TEXTSPLIT needs the text first, then the delimiter in double quotes.
Examples
=INDEX(TEXTSPLIT(A2," "),1,2)→ "Chen"On its own, =TEXTSPLIT(A2," ") puts Maya in the formula cell and spills Chen into the cell to its right. INDEX picks the second piece, the last name, when you only need one part.
=COUNTA(TEXTSPLIT(B2,";",,TRUE))→ 3The tags contain ;;, which would create an empty piece. Setting ignore_empty to TRUE skips it, so COUNTA counts 3 tags. The empty third argument skips row_delimiter.
=TEXTJOIN(", ",TRUE,TEXTSPLIT(B2,";",,TRUE))→ "red, blue, green"Splits the messy tag list, then joins the pieces back with a comma and a space: a quick way to swap one delimiter for another.
Example data
| 1 | Full name | Tags |
|---|---|---|
| 2 | Maya Chen | red;blue;;green |
Common mistakes
#SPILL! error
TEXTSPLIT needs empty cells to the right for its pieces. If anything is in the way, it shows #SPILL!. Clear those cells or move the formula.
Pieces start with a space
Splitting
"Leeds, LS1 4AB"at","leaves a space before LS1. Include the space in the delimiter,", ", or wrap the result in TRIM.Empty cells from double delimiters
Two delimiters in a row create an empty piece. Set the fourth argument,
ignore_empty, toTRUE.#NAME? in older Excel
TEXTSPLIT is a newer function, found in Microsoft 365 and Excel for the web. Excel 2021 and older show #NAME?. Use Text to Columns or LEFT, MID and FIND there.
FAQ
Can TEXTSPLIT split on more than one delimiter?+
Yes. Give it a list in curly brackets: =TEXTSPLIT(A2,{",",";"}) cuts at every comma and every semicolon.
How do I split text into rows instead of columns?+
Leave col_delimiter empty and use row_delimiter: =TEXTSPLIT(A2,,",") puts each piece in its own row.
What's the difference between TEXTSPLIT and Text to Columns?+
Text to Columns is a one-off command that overwrites the cells to the right. TEXTSPLIT is a formula, so it recalculates whenever the source text changes.
Lesson
Master TEXTSPLIT step by step: Modern Excel: XMATCH, SORTBY, TEXTSPLIT and spill references (A2#)
Split names with TEXTSPLIT, find positions with XMATCH, sort by another column with SORTBY, and refer to a whole spilled result with A2#.