What it does
TEXTSPLIT is the formula version of Data > Text to Columns. You type it in a single cell and the result spills to the right: as many cells fill up as there are parts. When the source changes, the parts change with it; you do not have to run the command again.
The second argument is the column delimiter; if you give a third, the text is split into rows as well. =TEXTSPLIT(A2,":",";") turns "Emma:West;Liam:North" into a table of two rows and two columns. If the rows have different numbers of parts, the gaps are filled with #N/A; the last argument lets you put another value there instead.
The cells the result spills into must be empty, otherwise the formula returns #SPILL!. TEXTSPLIT exists only in Microsoft 365, Excel 2024 and Excel for the web. If you need just one part, TEXTBEFORE or TEXTAFTER is shorter; to join parts again, use TEXTJOIN.
Syntax
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])=METİNBÖL(metin; sütun_sınırlayıcı; [satır_sınırlayıcı]; [boşları_yoksay]; [eşleştirme_modu]; [doldurma])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
text | Required | The text, or cell, to split. |
col_delimiter | Required | The delimiter that separates parts into columns, e.g. ", ". |
row_delimiter | Optional | The delimiter that separates parts into rows. If omitted, the result is a single row. |
ignore_empty | Optional | TRUE: an empty part between two delimiters is skipped. FALSE (default): an empty part still gets a cell. |
match_mode | Optional | 0 (default): delimiters are matched case-sensitively. 1: case is ignored. |
pad_with | Optional | The value written into the empty cells when rows have different numbers of parts. If omitted, #N/A appears. |
Examples
1Split an address into city, county and postcode
Each address sits in one cell as "City, County, Postcode". The formulas in column B spill to the right and write the parts into columns B, C and D, which is why C and D are left empty.
=TEXTSPLIT(A2,", ")| A | B | C | D | |
|---|---|---|---|---|
| 1 | Address | City | County | Postcode |
| 2 | Leeds, West Yorkshire, LS1 4AB | Leeds | West Yorkshire | LS1 4AB |
| 3 | York, North Yorkshire, YO1 7HU | York | North Yorkshire | YO1 7HU |
| 4 | Bristol, Avon, BS1 5TR | Bristol | Avon | BS1 5TR |
- B2 (the result spills into B2:D2)
=TEXTSPLIT(A2,", ") - B3 (the result spills into B3:D3)
=TEXTSPLIT(A3,", ") - B4 (the result spills into B4:D4)
=TEXTSPLIT(A4,", ")
The first row is split into Leeds, West Yorkshire and LS1 4AB.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Turn a list in one cell into a table
A record copied from another system consists of "Name:Region" pairs separated by semicolons. The colon is the column delimiter and the semicolon the row delimiter.
=TEXTSPLIT(A2,":",";")| A | B | C | D | |
|---|---|---|---|---|
| 1 | Record | Rep | Region | |
| 2 | Emma:West;Liam:North;Olivia:West;Noah:South | Emma | West | |
| 3 | Liam | North | ||
| 4 | Olivia | West | ||
| 5 | Noah | South |
- C2 (the result spills into C2:D5)
=TEXTSPLIT(A2,":",";")
Starting at C2, the formula spills into a table of four rows and two columns: C3 shows Liam, D3 North and C5 Noah.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Fill missing parts with a marker
One of the reps in the record has no region. Because "-" is given as the last argument, the empty cell on that row shows a hyphen instead of #N/A.
=TEXTSPLIT(A2,":",";",FALSE,0,"-")| A | B | C | D | |
|---|---|---|---|---|
| 1 | Record | Rep | Region | |
| 2 | Emma:West;Liam;Olivia:West | Emma | West | |
| 3 | Liam | - | ||
| 4 | Olivia | West |
- C2 (the result spills into C2:D4)
=TEXTSPLIT(A2,":",";",FALSE,0,"-")
On Liam's row, D3 shows -; the other rows get West and West.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #SPILL!
Cause: One of the cells the result needs is not empty, so the array cannot spill.
Fix: Clear the cells to the right of the formula (and below it, if you use a row delimiter).
- #N/A
Cause: The rows have different numbers of parts, and the missing cells were filled with #N/A.
Fix: Give the last argument a value:
=TEXTSPLIT(A2,":",";",FALSE,0,"-").- #NAME?
Cause: The file was opened in an older version of Excel that does not know TEXTSPLIT.
Fix: In older versions, use Data > Text to Columns.
Good to know
- To avoid spaces at the start of each part, include the space in the delimiter (
", ") or wrap the result in TRIM:=TRIM(TEXTSPLIT(A2,",")). - To take just one of the parts, use INDEX:
=INDEX(TEXTSPLIT(A2,", "),2)returns the second part. - The parts are always text; to use them as numbers, put two minus signs in front:
=--TEXTSPLIT(A2,";").
In Nexell
Get Nexell to do it from a sentence
Split the "City, County, Postcode" text in the Address column into three separate columns
Convert to tableBring your address list into the tool and type this. Nexell splits the address into city, county and postcode columns and shows them in a preview first; the source column stays as it is.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
What is the difference between TEXTSPLIT and Text to Columns?
The command splits the data once and writes the results as values; if the source changes, you have to run it again. TEXTSPLIT is a formula, so the parts update when the source cell changes.
Can TEXTSPLIT use more than one delimiter?
Yes, by passing an array constant such as {",",";"} as the delimiter. A simple alternative is to turn all delimiters into one with SUBSTITUTE first: =TEXTSPLIT(SUBSTITUTE(A2,";",","),",").
Related functions
Step-by-step guide: Text to Excel: split text into columns