Available in:Excel 2024 or later (Works in Excel 2024, every later version, Microsoft 365 and Excel for the web)Category:Text

TEXTSPLIT

TEKST.SPLITSEN in Dutch Excel

Cuts the text in one cell at every delimiter and spills the pieces into a row, a column or a two-dimensional grid.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: C2 holds the formula
Row	A	B	C	D	E	F
1	Posting key
2	INK\|25-0412\|2025-12-18\|1600		INK=TEXTSPLIT(A2, "\|")=TEXTSPLIT(A2; "\|")	25-0412	2025-12-18	1600

=TEXTSPLIT(A2, "|")

=TEXTSPLIT(A2; "|")

What TEXTSPLIT does and when to use it

What it does: it cuts the text in one cell at every delimiter you name and spreads the pieces over columns, rows or a grid, for example a posting key into its four parts.

When to use it: when one cell holds a packed value, such as a posting key, a list of account numbers or a pasted payment run. It replaces Text to Columns and long chains of LEFT, MID and FIND, and the pieces spill into the cells next to and below the formula. It reads one cell, not a column. For only the first or last piece, TEXTBEFORE or TEXTAFTER is shorter.

Two main gains:

Stays live: Text to Columns is a one-off action, while the formula updates when the source changes.
Columns and rows in one step: col_delimiter and row_delimiter together give a grid, and pad_with fills short rows.

How Excel evaluates it

Excel works in this order:

it looks for row_delimiter in text and cuts the text into rows
inside each row it cuts again at col_delimiter
it drops empty pieces between two delimiters when ignore_empty is TRUE
it compares the rows: the widest sets the width of the grid, and shorter rows are filled up with pad_with, or #N/A when you gave none

The result spills from the formula cell. If a cell in the spill area is occupied, you get #SPILL!.

Watch out: Every piece TEXTSPLIT returns is text, even when it looks like a number: SUM gives 0 and a comparison with a number is FALSE until you wrap the result in VALUE or multiply it by 1.

Mental model

A paper cutter with two blades: one blade cuts the strip into rows, the other cuts each row into cells, and the loose ends are padded to a rectangle.

Terms used on this page: spill, argument, cell

Syntax

Official TEXTSPLIT documentation

TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

TEXTSPLIT(text; col_delimiter; [row_delimiter]; [ignore_empty]; [match_mode]; [pad_with])

Arguments
Argument	Type	Meaning	Default
`text`	Required	The text to split: one cell or one text value.
`col_delimiter`	Required	Where to cut: each piece goes into the next column.
`row_delimiter`	Optional	Where to cut: each piece starts a new row.
`ignore_empty`	Optional	TRUE skips the empty piece between two delimiters.	`FALSE`
`match_mode`	Optional	0 is case-sensitive (default), 1 ignores case.	`0`
`pad_with`	Optional	What fills the gaps in short rows; default #N/A.	`#N/A`

Return value:An array of text pieces that spills from the formula cell; numbers inside it stay text

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Split a posting key into its four parts

In the XAF auditfile of Veldhuis Installatie BV, the client's export packs journal, entry number, date and account into one key per line. You want the four parts in separate cells so you can read them. The parts are separated by a vertical bar.

Sheet excerpt: C2 holds the formula
Row	A	C	D	E	F
1	Posting key
2	INK\|25-0412\|2025-12-18\|1600	INK=TEXTSPLIT(A2, "\|")=TEXTSPLIT(A2; "\|")	25-0412	2025-12-18	1600
3

Formula cell (C2)Spilled result

Formula in C2

=TEXTSPLIT(A2, "|")

=TEXTSPLIT(A2; "|")

Result: INK | 25-0412 | 2025-12-18 | 1600INK | 25-0412 | 2025-12-18 | 1600

Step by step

Excel reads A2 and finds the text INK|25-0412|2025-12-18|1600. The second argument is a vertical bar and there is no row_delimiter, so the result will be one row.
It finds the bar after INK, after 25-0412 and after 2025-12-18: three bars, so four pieces.
The pieces are INK, 25-0412, 2025-12-18 and 1600, in that order. Nothing sits between two bars, so no empty piece appears.
The result is 1 row by 4 columns and spills from C2 to F2: journal INK in C2, entry 25-0412 in D2, date text 2025-12-18 in E2 and account 1600 in F2.
Change A2 to VRK|25-0413|2025-12-19|8000 and the four cells update. The 1600 and the date are text, not a number or a date; F2 is left aligned.

Watch out: If C2:F2 already holds anything, the formula shows #SPILL!. Clear the cells to the right of the formula first.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

A delimiter is a boundary, so n delimiters always make n + 1 pieces. Because only col_delimiter is given, Excel lays the pieces side by side.

More to watch out for

The account 1600 comes back as text. A lookup of the number 1600 against F2 fails until you convert it with VALUE.

Variations

=TEXTSPLIT(A2, , "|")

=TEXTSPLIT(A2; ; "|")

Leave col_delimiter empty and fill row_delimiter instead: the same four parts, but in a column.

Intermediate

Clean up a typed list of accounts with double commas

The client's controller e-mailed the ledger accounts for the trial balance tie-out as one line of text. Some accounts are separated by two commas because items were removed by hand. You want only the real account numbers next to each other, without empty cells in between.

Sheet excerpt: C2 holds the formula
Row	A	C	D	E	F
1	Accounts received
2	1100,1300,,1600,,4000	1100=TEXTSPLIT(A2, ",", , TRUE)=TEXTSPLIT(A2; ","; ; TRUE)	1300	1600	4000
3

Formula cell (C2)Spilled result

Formula in C2

=TEXTSPLIT(A2, ",", , TRUE)

=TEXTSPLIT(A2; ","; ; TRUE)

Result: 1100 | 1300 | 1600 | 40001100 | 1300 | 1600 | 4000

Step by step

Excel reads A2: 1100,1300,,1600,,4000. The delimiter is a comma, row_delimiter is left empty and ignore_empty is TRUE.
Cutting at every comma gives six pieces: 1100, 1300, an empty piece, 1600, an empty piece, 4000. The empty pieces come from the double commas.
Because ignore_empty is TRUE, the two empty pieces are dropped: 1100, 1300, 1600, 4000 remain, four pieces.
The result spills from C2 to F2: 1100, 1300, 1600 and 4000, with no gaps. Without the TRUE, six cells spill from C2 to H2, with E2 and G2 empty.
Retype A2 as 4500,,8000 and the result becomes 4500 and 8000 in C2:D2.

Watch out: If an empty piece is information, for example a missing account that you must report, TRUE hides the evidence. Keep FALSE and count the empty cells instead.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

Two delimiters with nothing between them enclose an empty piece, and ignore_empty tells TEXTSPLIT to discard it before it builds the result. The empty argument between the commas keeps the fourth argument in its place without choosing a row delimiter.

More to watch out for

A space after each comma, as in "1100, 1300", is not part of the delimiter, so the pieces start with a space and a lookup on 1300 fails. Wrap the result in TRIM, as in TRIM(TEXTSPLIT(...)), or use ", " as delimiter.

Variations

=TEXTSPLIT(A2, , ",", TRUE)

=TEXTSPLIT(A2; ; ","; TRUE)

The same cleaned list as a column in C2:C5, which is better suited as lookup input.

Advanced

Turn a pasted payment run into a two-column table

A client pasted its payment run into one cell: invoice and amount per line, lines separated by a semicolon. The separator between invoice and amount is an equals sign in one line and a colon in another, and the last line has no amount yet. You want a table with invoice and amount, where the missing amount is marked instead of showing an error.

Sheet excerpt: C2 holds the formula
Row	A	C	D
1	Payment run
2	25-0412=3025;25-0413:15000;25-0414	25-0412=TEXTSPLIT(A2, {"=",":"}, ";", , , "missing")=TEXTSPLIT(A2; {"="\":"}; ";"; ; ; "missing")	3025
3		25-0413	15000
4		25-0414	missing
5

Formula cell (C2)Spilled result

Formula in C2

=TEXTSPLIT(A2, {"=",":"}, ";", , , "missing")

=TEXTSPLIT(A2; {"="\":"}; ";"; ; ; "missing")

Result: 25-0412 | 3025 / 25-0413 | 15000 / 25-0414 | missing25-0412 | 3025 / 25-0413 | 15000 / 25-0414 | missing

Step by step

Excel reads A2: 25-0412=3025;25-0413:15000;25-0414. The arguments are:
- column delimiters: the array constant {"=",":"}
- row delimiter: a semicolon
- pad_with: the text missing
The two empty arguments in between are ignore_empty and match_mode, left at their defaults.
First the row cut at the two semicolons gives three lines: 25-0412=3025, 25-0413:15000 and 25-0414.
Each line is cut at an equals sign or a colon, whichever occurs. Line 1 gives 25-0412 and 3025; line 2 gives 25-0413 and 15000; line 3 has neither character and stays one piece, 25-0414.
The widest line has 2 pieces, so the grid is 3 rows by 2 columns. Line 3 has only 1 piece, so its second cell is filled with pad_with.
The result spills from C2 to D4: 25-0412 and 3025; 25-0413 and 15000; 25-0414 and missing. Without the last argument, D4 would show #N/A.

Watch out: A trailing semicolon at the end of A2 silently adds a fourth row with an empty invoice and the padding text. The table looks complete; set ignore_empty to TRUE or remove the last character.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

The row delimiter is applied first and the column delimiters second, so each line is split on its own. An array constant lets one formula accept both separators the client used, and pad_with turns the ragged last line into a rectangle you can keep working on.

More to watch out for

The amounts 3025 and 15000 are text. SUM over D2:D4 gives 0; use VALUE on the column first, and expect an error on the padded cell.

Variations

=TEXTSPLIT(A2, {"=",":"}, ";", , , 0)

=TEXTSPLIT(A2; {"="\":"}; ";"; ; ; 0)

Pad with a 0 instead of a text when the next step is a calculation on the amount column.

Master

Total one supplier from a column of packed relation and amount texts

The payables list of Veldhuis Installatie BV arrives with relation code and open amount packed into one text per line, such as C2001=4200, and one line is blank. TEXTSPLIT reads one cell, so you first join the column.

You want the total open amount for the supplier in F1, with named steps so the reviewer can follow the construction.

Sheet excerpt: F2 holds the formula
Row	A	E	F
1	Relation and amount	Supplier	C2001
2	C2001=4200	Total open	€6,425.00€ 6.425,00=LET( grid, TEXTSPLIT(TEXTJOIN(";", TRUE, A2:A7), "=", ";"), relation, INDEX(grid, 0, 1), amount, VALUE(INDEX(grid, 0, 2)), SUM((relation = F1) * amount) )=LET( grid; TEXTSPLIT(TEXTJOIN(";"; TRUE; A2:A7); "="; ";"); relation; INDEX(grid; 0; 1); amount; VALUE(INDEX(grid; 0; 2)); SUM((relation = F1) * amount) )
3	C2002=1850
4
5	C2001=975
6	C2003=3100
7	C2001=1250
8

Formula cell (F2)Input

Formula in F2

=LET(
  grid, TEXTSPLIT(TEXTJOIN(";", TRUE, A2:A7), "=", ";"),
  relation, INDEX(grid, 0, 1),
  amount, VALUE(INDEX(grid, 0, 2)),
  SUM((relation = F1) * amount)
)

=LET(
  grid; TEXTSPLIT(TEXTJOIN(";"; TRUE; A2:A7); "="; ";");
  relation; INDEX(grid; 0; 1);
  amount; VALUE(INDEX(grid; 0; 2));
  SUM((relation = F1) * amount)
)

Result: €6,425.00€ 6.425,00

Step by step

LET names the first step, grid. Inside it, TEXTJOIN(";", TRUE, A2:A7) joins the six cells with semicolons and skips the blank A4 because ignore_empty is TRUE: C2001=4200;C2002=1850;C2001=975;C2003=3100;C2001=1250.
TEXTSPLIT cuts that text at the semicolons into 5 rows and each row at the equals sign into 2 columns. grid is a 5 by 2 block: C2001 and 4200; C2002 and 1850; C2001 and 975; C2003 and 3100; C2001 and 1250.
relation is INDEX(grid, 0, 1), the first column: C2001, C2002, C2001, C2003, C2001. amount is VALUE of the second column: 4200, 1850, 975, 3100, 1250, now real numbers.
In the last line, relation = F1 compares each relation with C2001: TRUE, FALSE, TRUE, FALSE, TRUE, which count as 1, 0, 1, 0, 1.
Multiplying by amount gives 4200, 0, 975, 0, 1250. SUM adds them: 4200 + 975 + 1250 = 6425. F2 shows 6425.
Change F1 to C2003 and the result is 3100. To debug, replace the last line by grid, then relation, then amount, and you see each named step spilled on its own.

Watch out: The mirror-image mistake is joining with ignore_empty FALSE. The blank A4 then leaves two semicolons in a row, TEXTSPLIT builds a row with an empty relation and #N/A as amount, VALUE turns that into #N/A, and SUM returns #N/A for the whole formula.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

TEXTJOIN turns the column into the single text that TEXTSPLIT needs, and LET keeps the split grid in one name so it is built once and read twice.

Do not use this on thousands of lines: the joined text has a limit of 32,76732.767 characters, and one helper column per part (TEXTBEFORE and TEXTAFTER) or Power Query is more robust and simpler to audit. The alternative in the variation avoids joining altogether.

More to watch out for

If a relation code or amount ever contains an equals sign or a semicolon, the split shifts silently and the total is wrong without an error. Check that the grid has exactly 2 columns with COLUMNS(grid).

Variations

=SUM(VALUE(TEXTAFTER(FILTER(A2:A7, LEFT(A2:A7, 5) = F1), "=")))

=SUM(VALUE(TEXTAFTER(FILTER(A2:A7; LEFT(A2:A7; 5) = F1); "=")))

No joining and no 32,76732.767 limit: filter the lines of the supplier, take the part after the equals sign per line, and sum. Prefer it when the relation code always has 5 characters.

Common mistakes

Common mistakes
Symptom	Cause	Fix
Only one text is split, not the whole column	You split a column of 200 cells with one TEXTSPLIT and expect one row of pieces per cell, but that is not what you get.	TEXTSPLIT reads one text value. Join the column first, TEXTSPLIT(TEXTJOIN(";", TRUE, A2:A200), "=", ";"), or use TEXTBEFORE and TEXTAFTER per row.
Calculations on the pieces go wrong	The pieces look like numbers, but calculations on them give the wrong result.	Wrap the result in VALUE, or multiply it by 1, before you calculate with it.
#N/A in some cells of the grid	Some cells in the grid show #N/A that are not errors in your data.	Short rows are padded with #N/A. Set pad_with to a text, a 0 or an empty text (two quotes), whichever fits what you do next.
Empty cells in the result	A double delimiter or a trailing delimiter leaves empty cells in the result and shifts later logic.	Set ignore_empty to TRUE when empty pieces carry no meaning, and keep it FALSE when a gap is information.

Used in combinations

Split a pasted list into a clean columnSplit a list of invoice numbers pasted into one cell on the commas, strip the stray spaces with TRIM and stack the pieces into one column with TOCOL, ready for matching.Excel 2024 or later (This combination needs Excel 2024 or later)