TEXTSPLIT Function in Excel 2007, 2010, 2013, 2016, 2019 & 2021

The TEXTSPLIT function was officially introduced with Excel 2021, but thanks to Modern Functions Update (MFU) you can now run it on your Excel 2007, 2010, 2013, 2016, 2019 and 2021 versions. It works exactly as its original Microsoft counterpart.

---

Description

The TEXTSPLIT function splits a text string into multiple values using specified delimiters. The result is a dynamic array that spills into adjacent cells – across columns, down rows, or both.

Think of it as the Text to Columns wizard, but in formula form and much more powerful. Unlike the wizard, TEXTSPLIT:

• Does not overwrite your original data
• Can split into both columns and rows simultaneously
• Supports multiple delimiters at once
• Updates automatically when source data changes

This is particularly useful for:

• Parsing imported data: Split CSV files, reports, or web data
• Cleaning names: Separate first and last names
• Extracting parts: Isolate specific components from structured text
• Data preparation: Prepare text for further analysis
• Creating dynamic arrays: Build flexible data structures

---

Syntax

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

Argument	Description
text	Required. The text string you want to split.
col_delimiter	Required. The character(s) that mark where to split the text across columns.
[row_delimiter]	Optional. The character(s) that mark where to split the text down rows.
[ignore_empty]	Optional. TRUE ignores consecutive delimiters (no empty cells); FALSE creates empty cells for them. Default is FALSE.
[match_mode]	Optional. 0 = case-sensitive (default); 1 = case-insensitive.
[pad_with]	Optional. Value to fill missing cells in 2D arrays. Default is #N/A.

---

Examples

Basic Usage – Split into Columns

To split text separated by commas into columns:

=TEXTSPLIT("John,Doe,30,New York", ",")

Returns: John | Doe | 30 | New York (spilled across four columns).

Split into Rows

To split text into rows instead of columns, leave col_delimiter empty and specify row_delimiter:

=TEXTSPLIT("John,Doe,30,New York", , ",")

Returns the same values in four rows.

Split into Both Rows and Columns (2D Array)

To split text into a 2D array, specify both delimiters:

=TEXTSPLIT("1,2,3;4,5,6", ",", ";")

Returns:

• Row 1: 1 | 2 | 3
• Row 2: 4 | 5 | 6.

Multiple Delimiters

Use an array constant {} to specify multiple delimiters:

=TEXTSPLIT("apple-orange,grape", {",", "-"})

Returns: apple | orange | grape (splits on both comma and hyphen).

Ignoring Empty Values

With consecutive delimiters:

=TEXTSPLIT("John,,Doe", ",")

Default behavior (ignore_empty = FALSE) returns: John | '' | Doe (with an empty cell).

To remove the empty cell:

=TEXTSPLIT("John,,Doe", ",", , TRUE)

Returns: John | Doe (no empty cell).

Case-Insensitive Matching

If you need to split on “x” regardless of case:

=TEXTSPLIT("4 FT 0", " ft ", , , 1)

Returns: 4 | 0 (finds “FT” ignoring case).

Padding Missing Values

In a 2D array where some values are missing:

=TEXTSPLIT("Red-Blue;Green", "-", ";", , , "N/A")

If a value is missing, "N/A" appears instead of #N/A.

Using Cell References

=TEXTSPLIT(A1, ",")

Splits the text in cell A1 using comma as delimiter.

With Other Functions

Extract the third item from a split:

=INDEX(TEXTSPLIT(A1, ","), 1, 3)

Returns the third element from the split array.

Parsing Dates

=TEXTSPLIT(TEXT(B3, "m/d/yyyy"), "/")

Splits a date into month, day, and year components.

---

TEXTSPLIT vs Text to Columns Wizard

Feature	TEXTSPLIT	Text to Columns Wizard
Preserves original data	✅ Yes	❌ No (overwrites)
Row delimiter support	✅ Yes	❌ No
Multiple delimiters	✅ Yes	❌ No
Dynamic updates	✅ Yes	❌ No
Formula-based	✅ Yes	❌ No (manual)

---

Common Problems

Problem	What It Means	What To Do
#NAME? error	Excel doesn’t recognize TEXTSPLIT	Install Modern Functions Update
#SPILL! error	The spill range is blocked	Clear cells where the result wants to spill
#N/A in output	Missing value in 2D array with no pad_with	Add a pad_with value
Original text returned	Delimiter not found	Check your delimiter spelling/characters

---

Notes

• ✅ Fully tested – The implementation in Modern Functions Update works identically to Microsoft’s original function introduced in Excel 2024/Microsoft 365.
• ✅ The function is fully compatible with Excel 2007, 2010, and all later versions.
• ℹ️ At least one delimiter (col_delimiter or row_delimiter) must be provided.
• ℹ️ If both delimiters are the same, col_delimiter is prioritized.
• ℹ️ TEXTSPLIT is the inverse of TEXTJOIN – it splits what TEXTJOIN combines.
• ⚠️ TEXTSPLIT is a dynamic array function – results spill into adjacent cells.
• ℹ️ To extract a single value before or after a delimiter, consider TEXTBEFORE or TEXTAFTER.

---

See Also

• TEXTBEFORE – Returns text before a delimiter
• TEXTAFTER – Returns text after a delimiter
• TEXTJOIN – Joins text with delimiters
• UNIQUE – Returns unique values from a range
• FILTER – Filters a range based on criteria
• IFNA – Handles #N/A errors gracefully