TEXTSPLIT in Excel: how to use it
TEXTSPLIT splits text across columns or rows wherever a delimiter appears. One formula, and the pieces spill over automatically. Available in Excel 365 and 2024.
Try the first exercises for freeIn the lesson Excel 365 for advanced users
What does TEXTSPLIT do?
TEXTSPLIT spreads text over several cells, each time there's a delimiter, such as a comma or a space. You type the formula in one cell, and the pieces spill over into the cells next to it.
With the third argument, you split across rows instead of columns. If you give both a column and a row delimiter, Excel turns it into a whole table. TEXTSPLIT works in Excel 365 and Excel 2024. In an older Excel, use Data > Text to Columns.
When do you use TEXTSPLIT?
- Splitting a first name, surname and town that are in one cell with commas over three columns: =TEXTSPLIT(A2,",").
- Putting a list of names from one cell underneath each other: =TEXTSPLIT(A2,,", ").
- Counting how many words there are in a sentence: =COUNTA(TEXTSPLIT(A2," ")).
- The middle piece of a code such as UK-204-17: =CHOOSECOLS(TEXTSPLIT(A2,"-"),2).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Filled in: =TEXTSPLIT(A2,",")
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
TEXTSPLIT | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | TEXTSPLIT |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
text | The text you want to split, usually a cell such as A2. | A2 |
col_delimiter | The character where each new column starts, in quotation marks, such as "," or " ". Leave it empty, and you only split across rows. | "," |
[row_delimiter] | Optional: the character where each new row starts. | left out |
[ignore_empty] | Optional: with TRUE, Excel skips empty pieces, such as between two commas in a row. Without this argument, you get an empty cell there. | left out |
[match_mode] | Optional: with 1, Excel ignores capitals, so "x" and "X" both split. Without this argument, the difference counts. | left out |
[pad_with] | Optional: what Excel puts in the cells that stay empty when not every row has the same number of pieces. Without this argument, that's #N/A. | left out |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. | |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. |
TEXTSPLIT step by step
C2 contains =TEXTSPLIT(A2,","). Lucy, Walker and Leeds go into C2, D2 and E2. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click C2, with empty cells next to it, and type TEXTSPLIT with a bracket.
=TEXTSPLIT( - Click A2, the text you want to split, and type a comma.
=TEXTSPLIT(A2, - Type the delimiter in quotation marks: ",".
=TEXTSPLIT(A2,"," - Close the bracket and press Enter: Lucy, Walker and Leeds each go into a cell of their own.
=TEXTSPLIT(A2,",")
More examples
Underneath each other
C2 contains =TEXTSPLIT(A2,,", "). The delimiter is the third argument: the four names go underneath each other, starting in C2.
Counting words
C2 contains =COUNTA(TEXTSPLIT(A2," ")). TEXTSPLIT turns the sentence into six pieces, and COUNTA counts them.
Skipping empty pieces
C2 contains =TEXTSPLIT(A2,",",,TRUE). Because of the two commas in a row, there would be an empty cell in between. With TRUE as the fourth argument, Cambridge goes straight into D2.
Where it often goes wrong
Practise this in the lesson Excel 365 for advanced users. The first exercises are free.
Frequently asked questions
What if I have an older version of Excel?
Then use Data > Text to Columns. That splits the text once, without a formula: if the text changes, you have to split it again.
Read next
LEFT, RIGHT and MIDTRIMLENUPPER, LOWER and PROPERSEARCH and FIND