Excel TEXTSPLIT Function

TEXTSPLIT splits a text string into separate columns or rows based on one or more delimiters you specify, then spills the results into neighboring cells automatically. Unlike the Text to Columns wizard, it's a live formula: change the source cell and the split updates on its own.

Available in: Excel 365, Excel 2024, and Excel for the web. Not available in Excel 2021, 2019, or earlier.

Syntax

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

Parameter	Required	Description
text	Yes	The string to split. Can be a cell reference, a literal string, or the result of another formula.
col_delimiter	Yes	The character or characters that mark where a new column starts. Pass "" here if you only want to split by rows.
row_delimiter	No	The character or characters that mark where a new row starts. Leave it out to keep everything on one row.
ignore_empty	No	TRUE skips over blank results caused by two delimiters in a row. Defaults to FALSE, so consecutive delimiters produce empty entries.
match_mode	No	0 for case-sensitive delimiter matching (the default), 1 for case-insensitive. Matters when the delimiter is a word, like splitting on "and".
pad_with	No	The value inserted into empty positions when a two-dimensional split produces uneven rows or columns. Defaults to #N/A.

Basic Example

You export leads from a signup form into one column, each entry formatted like "Jane Chen, jane@acme.com, Enterprise." You need name, email, and plan in separate columns.

=TEXTSPLIT(A2, ", ")

// A2    = the raw text string "Jane Chen, jane@acme.com, Enterprise"
// ", "  = comma-space marks where each new column starts

TEXTSPLIT reads A2 left to right and breaks the string every time it hits ", ". The formula returns three values: "Jane Chen" in the cell where you typed the formula, "jane@acme.com" one column over, and "Enterprise" one column after that. No row_delimiter needed here since everything stays on a single row.

How TEXTSPLIT Works

TEXTSPLIT spills results across cells

You write the formula once, in one cell, and Excel fills in as many cells as the split requires. If a neighboring cell already has data in it, the formula returns #SPILL! instead of overwriting anything. Clear the blocking cell and the formula fills automatically.

Row delimiters stack results vertically

Add a row_delimiter to turn one string into a vertical list instead of a horizontal one.

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

// A2   = "Complete task;Review draft;Send invoice"
// ""   = col_delimiter left empty, since no column split is needed
// ";"  = row_delimiter, breaks the string into a vertical list

Each task lands in its own row below the formula cell rather than spreading across columns.

Multiple delimiters need an array constant

Wrap the delimiters in curly braces to split on more than one character at once.

=TEXTSPLIT(A2, {",", ";", "/"})

TEXTSPLIT checks every position in the text against each item in the array. Any one of the three characters triggers a new split. This is the only way to handle text with mixed punctuation, like addresses typed by different people over the years.

ignore_empty controls consecutive delimiters

Set the fourth argument to TRUE when your source data has doubled-up delimiters. A string like "Boston,,Chicago,Denver" produces a blank cell between Boston and Chicago by default.

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

TRUE collapses that blank and shifts every real value one position left, so you end up with three cities instead of three cities and a gap.

match_mode controls case sensitivity

By default, TEXTSPLIT matches delimiters case-sensitively. Splitting "Marketing and Sales AND Finance" on the word " and " with the default match_mode only breaks on the lowercase instance, leaving "AND Finance" stuck together.

=TEXTSPLIT(A2, " and ", , , 1)

Setting match_mode to 1 makes the delimiter match regardless of case, so both "and" and "AND" trigger a split.

pad_with fills gaps in two-dimensional splits

When a single string is split by both col_delimiter and row_delimiter at once, some rows can end up with fewer pieces than others.

=TEXTSPLIT(B2, ";", ",", , , "")

// B2 = "Alpha,Beta,Gamma;Delta,Epsilon"
// ";" splits into 2 rows
// "," splits each row into columns, but row 2 has only 2 pieces to row 1's 3

Without pad_with set, the shorter row shows #N/A in its missing position. Setting it to "" fills that gap with blank text instead, so the grid looks clean rather than error-riddled. This is a different situation from feeding TEXTSPLIT a whole range of cells as text, which runs into a separate limitation covered in the gotchas below.

Common Use Cases

Cleaning up a CRM export

A signup form dumps every lead into one raw text column. Split it into structured fields for a pivot table.

=TEXTSPLIT(leads[Raw_Entry], ", ")   // spills into name, email, and plan columns per lead

Turning a whole column of entries into one clean table

TEXTSPLIT applied directly to a multi-row range doesn't reliably return one flat table, since each row can split into a different number of pieces. This is the array of arrays limitation. Process one row at a time with REDUCE and stack the results with VSTACK instead.

=DROP(REDUCE(0, orders, LAMBDA(acc, row, VSTACK(acc, TEXTSPLIT(row, "|", , , , "")))), 1)

// orders  = B2:B500, one delimited order string per row
// REDUCE  = processes each row's split result and stacks it under the last
// DROP(...,1) removes the placeholder 0 used to start REDUCE

Parsing inconsistent delimiters from legacy data

Shipping addresses entered over several years mix commas, semicolons, and slashes: "221B Baker St; London/NW1 6XE" next to "10 Downing St, London, SW1A 2AA."

=TEXTSPLIT(A2, {",", ";", "/"}, , TRUE)

// {",", ";", "/"}  = checks each position against three possible delimiters
// TRUE             = ignore_empty, so double delimiters don't leave blank cells

Handling Errors

TEXTSPLIT throws #N/A when a two-dimensional split produces rows or columns of different lengths and pad_with isn't specified. It throws #SPILL! when the result needs more free cells than are available around the formula.

Common causes of #N/A:

• Splitting a range of cells where each row breaks into a different number of pieces, with pad_with left at its default
• A delimiter that doesn't appear in one particular row, leaving nothing to fill that position

Common causes of #SPILL!:

• Cells below or to the right of the formula already contain data
• The source text needs more columns than fit before hitting the edge of the worksheet

=TEXTSPLIT(B2:B10, ",", , FALSE, 0, "")   // pads short rows with an empty string instead of #N/A

Notes & Gotchas

How do you use the TEXTSPLIT function in Excel?

Type =TEXTSPLIT( followed by the cell with your text and the delimiter you want to split on, like =TEXTSPLIT(A2, ","). Press Enter once, not Ctrl+Shift+Enter, since TEXTSPLIT spills automatically. Excel fills the results into as many cells to the right, or below with a row_delimiter, as the split requires.

What is the difference between TEXTSPLIT and Text to Columns?

Text to Columns is a one-time wizard. It splits data in place and doesn't update when the source changes. TEXTSPLIT is a formula, so it recalculates automatically, and it can spill into rows as well as columns, something Text to Columns can't do in a single pass.

Is TEXTSPLIT available in older versions of Excel?

No, with one exception. TEXTSPLIT works in Microsoft 365 and in Excel 2024, the newer perpetual release. It's not included in Excel 2021, Excel 2019, or any earlier perpetual license, even though those versions ship with many other modern functions. It does work in Excel for the web when you're signed into a 365 account.

How do you split text by multiple delimiters in Excel using TEXTSPLIT?

Wrap the delimiters in curly braces to pass them as an array constant, like =TEXTSPLIT(A2, {",", ";", "/"}). TEXTSPLIT checks every character position against each item in the array, so any one of the characters triggers a split. This is the only way to handle more than one delimiter in a single formula.

How do you ignore empty values when using TEXTSPLIT?

Set the fourth argument, ignore_empty, to TRUE. By default it's FALSE, so two delimiters in a row, like a double comma, produce a blank cell in the results. TRUE collapses those blanks and shifts every real value one position left.

Why does TEXTSPLIT return numbers as text?

TEXTSPLIT always returns text, even when the piece it splits off looks like a number. A result of "42" is a text string, not the number 42, so SUM or AVERAGE run on it directly treats it as zero. Multiply the result by 1, or wrap it in VALUE, to force a real number: =TEXTSPLIT(A2,",")*1.

What happens when you use TEXTSPLIT on a whole range of cells?

TEXTSPLIT splits each cell independently and tries to combine the results into a single two-dimensional array, sometimes called an "array of arrays." When rows split into different numbers of pieces, Excel pads the shorter rows with #N/A, or in some cases returns a result that looks truncated. Set pad_with explicitly, or restructure the split with REDUCE and VSTACK, to get a clean, consistent table.

Why does TEXTSPLIT fail on long text?

TEXTSPLIT can't process a string longer than 32,767 characters, the same limit that applies to any single cell in Excel. This rarely matters for names or addresses but shows up when someone pastes a full paragraph or a block of HTML into one cell. If you hit this, break the source text into smaller chunks before splitting.

Related Functions

Function	Use this when...
TEXTJOIN	You need to reverse the process and combine separate values back into one delimited string.
FILTER	You want to narrow down a range first, then split or process only the matching rows.