Skip to content

Excel function · Text

Excel TEXTSPLIT function

TEXTSPLIT splits the text in a cell at the delimiter you give it and writes the parts into neighbouring cells, and into rows too if you like: "Leeds, West Yorkshire, LS1 4AB" becomes three cells. It is available in Microsoft 365 and Excel 2024.

In Turkish Excel: METİNBÖL

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

English Excel=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
Turkish Excel=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

ArgumentRequiredDescription
textRequiredThe text, or cell, to split.
col_delimiterRequiredThe delimiter that separates parts into columns, e.g. ", ".
row_delimiterOptionalThe delimiter that separates parts into rows. If omitted, the result is a single row.
ignore_emptyOptionalTRUE: an empty part between two delimiters is skipped. FALSE (default): an empty part still gets a cell.
match_modeOptional0 (default): delimiters are matched case-sensitively. 1: case is ignored.
pad_withOptionalThe 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.

B2fx=TEXTSPLIT(A2,", ")
Split an address into city, county and postcode: example table
ABCD
1AddressCityCountyPostcode
2Leeds, West Yorkshire, LS1 4ABLeedsWest YorkshireLS1 4AB
3York, North Yorkshire, YO1 7HUYorkNorth YorkshireYO1 7HU
4Bristol, Avon, BS1 5TRBristolAvonBS1 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.

C2fx=TEXTSPLIT(A2,":",";")
Turn a list in one cell into a table: example table
ABCD
1RecordRepRegion
2Emma:West;Liam:North;Olivia:West;Noah:SouthEmmaWest
3LiamNorth
4OliviaWest
5NoahSouth
  • 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.

C2fx=TEXTSPLIT(A2,":",";",FALSE,0,"-")
Fill missing parts with a marker: example table
ABCD
1RecordRepRegion
2Emma:West;Liam;Olivia:WestEmmaWest
3Liam-
4OliviaWest
  • 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

Type this in Nexell

Split the "City, County, Postcode" text in the Address column into three separate columns

Convert to table

Bring 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

All text functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free