TEXTSPLIT in Excel: Syntax, Examples and Errors

Syntax

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

Depending on your regional settings, arguments are separated with semicolons instead of commas: =TEXTSPLIT(A2; ","). This is standard in countries where the comma is the decimal separator.

Basic examples

=TEXTSPLIT(A2, ",")

The everyday split: a cell holding "Smith, John" comes back as two cells side by side, spilled automatically. No wizard, no paste — the formula keeps recalculating whenever A2 changes.

=TEXTSPLIT(A2, , ",")

Leave col_delimiter empty and give a row delimiter instead: the same comma list now spills downward, one value per row. A quick way to unpack a single cell into a column — addresses, tags, any packed list.

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

An array constant accepts several delimiters at once: commas and semicolons both split, in any mix. No need to nest TEXTSPLIT inside itself — one formula handles inconsistent data.

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

With ignore_empty set to TRUE, consecutive delimiters collapse: "a,,b" splits into two cells, not three with an empty middle. The default FALSE keeps one slot per blank — switch to TRUE when the gaps are noise.

Every piece TEXTSPLIT returns is text — the "123" spilled out of a cell is the string 123, not the number. Wrap pieces in VALUE before math, or keep the source column and convert in place.

Common errors and how to fix them

The split-out numbers are text: what TEXTSPLIT returns for "123" is a string, and SUM acts like it is not there. Wrap results in VALUE for math, or fix text-stored numbers in the source column first.

• Trim whitespace in ExcelDelimiters that refuse to match are often tripping over invisible spaces — clean the source text first.
• Convert text to numbersTurn the text-stored numbers that spill out of a split back into real numbers your formulas can calculate with.

TEXTSPLIT vs. Text to Columns

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

One formula takes a column delimiter and a row delimiter together and spreads packed text straight into a grid — the wizard can only go one direction at a time and never refreshes with the data. Use the wizard for known, one-shot, in-place jobs; use TEXTSPLIT when you want it formula-driven, refreshing, and two-directional.

TEXTSPLIT in Google Sheets

Google Sheets has no TEXTSPLIT — the equivalent is SPLIT, which also spills one cell into an array of results, but with a different argument order and defaults.

• The basic form is =SPLIT(A2, ","): the second argument is the delimiter. Note that the third argument split_by_each defaults to TRUE and treats the delimiter character by character — writing ",;" there splits on commas and semicolons at once, like TEXTSPLIT's array constant.
• remove_empty_text is the counterpart of ignore_empty: =SPLIT(A2, ",", TRUE, TRUE) collapses consecutive delimiters with TRUE as the fourth argument.
• The result is also a spilling array that recalculates when the source changes — same behavior as TEXTSPLIT on the Excel side.
• In the other direction, TEXTJOIN exists in Sheets too: join packed lists back together with TEXTJOIN, break them apart with SPLIT.

Limitations and pro tips

• TEXTSPLIT only exists in Microsoft 365 and Excel 2024 onward — Excel 2021, 2019 and 2016 have nothing like it, and #NAME? is the version signal.
• The result is one dynamic array: you cannot edit a single cell inside it, the whole block changes together. To reference the whole spill, use the spill reference A2#.
• Uneven grids are padded with #N/A by default: pass "" or a placeholder to pad_with so reports do not sprout error codes.
• Split-out numbers and dates are text: SUM and AVERAGE ignore them until you convert with VALUE.
• Line breaks inside a cell split too: CHAR(10) as the row delimiter turns one packed cell into several rows — remember to enable Wrap Text.

Treat TEXTSPLIT as the last mile of data ingestion: clean the source text first (spaces, line breaks, consistent delimiters), then split. The quality of the grid depends on the string you feed it — clean source, tidy grid.

• Merge columns in ExcelThe opposite direction, or merging cleaned pieces back together — no formulas needed.
• Split text to columnsFor one-off jobs, skip the formula entirely and split a whole column in a couple of clicks.