Excel TEXTSPLIT Function

last modified October 5, 2026

The TEXTSPLIT function is a dynamic array function in Excel that splits a text string into multiple cells using column and row delimiters. It works like the Text to Columns wizard, but as a formula, and it is the inverse of TEXTJOIN. The result spills into neighbouring cells and recalculates automatically when the source text changes. This tutorial covers the syntax, column and row delimiters, multiple delimiters, the optional ignore_empty, match_mode, and pad_with arguments, number conversion, and how to combine TEXTSPLIT with functions such as LET and HSTACK.

TEXTSPLIT Function Basics

The TEXTSPLIT function divides text into separate cells using delimiters you specify. A col_delimiter splits the text across columns, a row_delimiter splits it down rows, and supplying both produces a two-dimensional array. It is particularly useful for parsing CSV data, log files, and other structured text.

Component	Description
Function Name	TEXTSPLIT
Syntax	=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
Required Arguments	text, col_delimiter
Optional Arguments	row_delimiter, ignore_empty, match_mode, pad_with
Return Value	A spilled array of split text values
Availability	Excel for Microsoft 365, Excel for the web, Excel 2024, and Excel 2024 for Mac

This table breaks down the essential components of the TEXTSPLIT function. It shows the function name, complete syntax format, argument requirements, and return value characteristics.

The table below describes each argument in more detail.

Argument	Meaning
text	The text string to split. It can be a literal in quotes, a cell reference, or an expression that returns text.
col_delimiter	The character or characters that mark where to split the text across columns. Required unless a row_delimiter is supplied.
row_delimiter	Optional. The character or characters that mark where to split the text down rows. Leave the argument empty when you do not need it.
ignore_empty	Optional. TRUE ignores consecutive delimiters; FALSE, the default, keeps an empty string for each missing value.
match_mode	Optional. 0 performs a case-sensitive match, the default; 1 performs a case-insensitive match.
pad_with	Optional. The value used to pad missing cells in an uneven two-dimensional result. The default is the #N/A error.

TEXTSPLIT is not available in Excel 2021 or earlier; older versions return a #NAME? error. Because the result is a dynamic array, it needs enough empty cells to spill into, otherwise Excel reports a #SPILL! error. Both delimiter arguments also accept arrays, so you can split on several characters at once.

Basic TEXTSPLIT Example

This example demonstrates the simplest use of TEXTSPLIT with a single delimiter to split text into columns.

=TEXTSPLIT("Apple,Orange,Banana", ",")

This formula splits the text "Apple,Orange,Banana" at each comma. The result spills horizontally into three cells: "Apple", "Orange", and "Banana". The output values are always text, even when they look like numbers.

Column Delimiters vs Row Delimiters

The first delimiter splits text across columns and the second splits it down rows. The same delimiter produces the same values in a different direction, depending only on which argument it occupies.

A	B
Apple,Orange,Banana	=TEXTSPLIT(A1, ",")

=TEXTSPLIT(A1, ",")

The comma occupies the col_delimiter position, so the three values spill to the right of the formula cell.

=TEXTSPLIT(A1, , ",")

Here the col_delimiter argument is left empty and the comma moves to the row_delimiter position. The values now spill downward from the formula cell. Provide a column delimiter for side-by-side results and a row delimiter for a vertical list.

TEXTSPLIT with Row and Column Delimiters

TEXTSPLIT can split text into both rows and columns when both delimiters are provided. This example demonstrates this powerful feature.

A	B
Apple,Orange|Banana,Grape
	=TEXTSPLIT(A1, ",", "|")

This table demonstrates TEXTSPLIT's ability to create a 2D array from text. The comma splits columns while the pipe character splits rows.

=TEXTSPLIT(A1, ",", "|")

The formula splits A1 into a 2x2 array. First row: "Apple", "Orange". Second row: "Banana", "Grape". Excel first cuts the text at each pipe to form the rows, then cuts each row at each comma to form the columns.

TEXTSPLIT with Multiple Delimiters

TEXTSPLIT can handle multiple delimiter characters simultaneously. Here's an example using both commas and semicolons.

A	B	C
Name:John;Age:30,City:NY
	=TEXTSPLIT(A1, {",",";"})

The table shows how TEXTSPLIT can split text using multiple delimiters. The formula in B2 splits the text at both commas and semicolons.

=TEXTSPLIT(A1, {",",";"})

This formula splits the text in A1 at both commas and semicolons. The result is three cells: "Name:John", "Age:30", and "City:NY". When you need more than one delimiter, you must supply them as an array constant in curly braces, and such a delimiter array works for both the column and the row argument.

TEXTSPLIT with Ignore Empty

The ignore_empty argument controls whether empty values between consecutive delimiters are kept or removed. FALSE, the default, keeps them; TRUE skips them. This example shows both behaviors.

A	B
Red,,Green,,Blue	=TEXTSPLIT(A1, ",", , FALSE)
	=TEXTSPLIT(A1, ",", , TRUE)

The table shows the same text split twice. B1 sets ignore_empty to FALSE and B3 sets it to TRUE, so the two results do not block each other's spill.

=TEXTSPLIT(A1, ",", , FALSE)
=TEXTSPLIT(A1, ",", , TRUE)

The first formula spills five cells: "Red", an empty string, "Green", another empty string, and "Blue". The second formula skips the empty values and spills only three cells: "Red", "Green", and "Blue". Setting the argument to TRUE is a quick way to clean up data with doubled delimiters.

TEXTSPLIT with Match Mode

The match_mode argument controls whether delimiters are matched case-sensitively. The default 0 is case-sensitive and 1 ignores letter case. This example demonstrates both options with the delimiter " x ".

A	B
apple X orange x BANANA x grape	=TEXTSPLIT(A1, " x ", , , 0)
	=TEXTSPLIT(A1, " x ", , , 1)

The table shows TEXTSPLIT with different match_mode settings. B1 uses the default exact matching while B2 ignores case.

=TEXTSPLIT(A1, " x ", , , 0)
=TEXTSPLIT(A1, " x ", , , 1)

With match_mode 0 the formula matches only the lowercase " x " separators and returns three cells: "apple X orange", "BANANA", and "grape". With match_mode 1 it also matches " X ", so the text splits into four cells: "apple", "orange", "BANANA", and "grape". Case-insensitive matching is useful when the delimiter case varies in the source data.

TEXTSPLIT with Padding

When a two-dimensional split produces uneven rows, the pad_with argument fills the missing cells. Without it, Excel pads with the #N/A error. This example shows padding in action.

A	B
Apple,Orange|Banana|Grape,Peach,Mango
	=TEXTSPLIT(A1, ",", "|", , , "N/A")

The table demonstrates how TEXTSPLIT handles uneven splits. The formula pads missing values with "N/A" to create a rectangular array output.

=TEXTSPLIT(A1, ",", "|", , , "N/A")

This creates a 3-row, 3-column array. First row: "Apple", "Orange", "N/A". Second row: "Banana", "N/A", "N/A". Third row: "Grape", "Peach", "Mango". The first row holds two values and the second row only one, so each is padded to the width of the longest row. Without pad_with the same cells would contain #N/A; wrapping the formula in IFNA(..., "") turns them into empty strings.

TEXTSPLIT with Numbers

TEXTSPLIT always returns text. Numbers inside the source string arrive as text values, left-aligned and ignored by SUM, until you convert them.

A	B
10,20,30	=SUM(--TEXTSPLIT(A1, ","))

=TEXTSPLIT(A1, ",")
=VALUE(TEXTSPLIT(A1, ","))
=SUM(--TEXTSPLIT(A1, ","))

The first formula returns the text values "10", "20", and "30". The second converts them to real numbers with VALUE. The third uses the double unary operator --, which coerces text to numbers in one step, and returns 60. Adding +0 to the result works the same way.

TEXTSPLIT with LET and HSTACK

TEXTSPLIT works seamlessly with Excel's dynamic arrays. This example combines it with LET, INDEX, and HSTACK to parse a small record into named results.

=LET(
    t, "John Smith|90,85,78",
    parts, TEXTSPLIT(t, "|"),
    name, INDEX(parts, 1),
    nums, VALUE(TEXTSPLIT(INDEX(parts, 2), ",")),
    HSTACK(name, SUM(nums), MAX(nums))
)

The formula splits the record at the pipe, takes the first piece as the name, then splits the second piece at commas and converts it to numbers. The final output is one row of three values: "John Smith", 253, and 90. Each TEXTSPLIT call receives a single text value, which keeps the result a simple array.

One limitation matters when splitting several cells at once. Applied to a multi-cell range, TEXTSPLIT returns an "array of arrays", which the formula engine cannot spill correctly and truncates. Split one cell per formula, or stack the per-cell results with REDUCE and VSTACK:

=DROP(REDUCE("", A1:A3, LAMBDA(acc, x, VSTACK(acc, TEXTSPLIT(x, ",")))), 1)

REDUCE visits A1:A3 cell by cell, VSTACK appends each split row to the accumulator, and DROP removes the placeholder row created by the initial empty string.

TEXTSPLIT vs Other Splitting Methods

Excel offers several ways to divide text, and each has a different purpose. This table compares TEXTSPLIT with the closest alternatives.

Method	Returns	Notes
TEXTSPLIT	All pieces at once, as a spilled array	Live formula; supports column, row, and multiple delimiters
TEXTBEFORE	The text before a chosen occurrence of a delimiter	Best when you need a single leading piece
TEXTAFTER	The text after a chosen occurrence of a delimiter	Best when you need a single trailing piece
FILTERXML	Nodes of an XML string matched by an XPath	Excel 2013+, Windows desktop only
Text to Columns wizard	Static values written onto the sheet	One-off operation; does not update with the data
LEFT, MID, FIND	A single extracted substring per formula	Works in older versions but needs one formula per piece

Use TEXTBEFORE and TEXTAFTER when you need just one piece of the text, and TEXTSPLIT when you need every piece. For example, =TEXTBEFORE(A1, ",") returns "Apple" from "Apple,Orange,Banana", while =TEXTSPLIT(A1, ",") returns all three values. FILTERXML remains the tool for XML payloads, and the Text to Columns wizard is the fallback for Excel versions without TEXTSPLIT.

TEXTSPLIT Practical Application

This example shows a real-world application of TEXTSPLIT for parsing a log entry into date, time, level, and message columns.

=LET(
    log, "2023-05-15 14:30:22 [ERROR] Connection timeout in ModuleA",
    parts, TEXTSPLIT(log, " ", , TRUE),
    level, TEXTBEFORE(TEXTAFTER(INDEX(parts, 3), "["), "]"),
    msg, TEXTAFTER(log, "] "),
    HSTACK(INDEX(parts, 1), INDEX(parts, 2), level, msg)
)

The formula splits the log line at spaces, using ignore_empty TRUE so double spaces in real logs do not create empty cells. The first two pieces are the date and time, the third piece is the level in square brackets, which TEXTBEFORE and TEXTAFTER strip, and the message is everything after the "] " separator. The result is a single row of four columns:

Date	Time	Level	Message
2023-05-15	14:30:22	ERROR	Connection timeout in ModuleA

Because the whole parse is one formula, editing the source log updates every output column instantly.

TEXTSPLIT Errors and Troubleshooting

Cause	Symptom	Fix
Excel 2021 or earlier	#NAME? error	Use Microsoft 365 or Excel 2024, or the Text to Columns wizard
Spill range contains other data	#SPILL! error	Clear the cells the result needs or move the formula
Delimiter does not occur in the text	Original text returned unchanged	Check the delimiter for typos, wrong characters, or extra spaces
Spaces after delimiters in the source	Results contain leading spaces	Wrap the formula in TRIM
Numbers needed for calculations	Results are text and SUM ignores them	Convert with VALUE, the -- operator, or +0
TEXTSPLIT applied to a multi-cell range	Truncated result from the array-of-arrays limitation	Split one cell per formula or use REDUCE with VSTACK
Uneven rows without pad_with	#N/A in the missing cells	Supply pad_with or wrap the formula in IFNA

To trap any TEXTSPLIT error in a single cell, wrap the whole formula in IFERROR. Remember that a missing delimiter is not an error: the function quietly returns the original text, which is why troubleshooting usually starts with the delimiter itself.

The TEXTSPLIT function brings the Text to Columns wizard into the formula language. With a single delimiter it replaces a row of helper formulas; with column and row delimiters it builds whole two-dimensional arrays; and with multiple delimiters, ignore_empty, match_mode, and pad_with it handles messy real-world text. The result spills and recalculates automatically, so the parsed output survives edits to the source data. Remember that the output is always text, that numbers need VALUE or a double unary conversion, and that the function requires Excel 2024 or Microsoft 365. Mastering TEXTSPLIT will make short work of CSV imports, log files, and any other delimited data in your spreadsheets.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.