How to Use the TEXTSPLIT Function in Excel to Separate Delimited Text by Rows and Columns

Splitting delimited strings—such as comma-separated values, customer addresses, SKU codes, or email headers—is a foundational data preparation task in Microsoft Excel. For decades, users relied on the legacy Text to Columns wizard, which overwrites adjacent data, lacks formula dynamic recalculation, and cannot split data vertically into rows. In Excel 365 and Excel 2024, Microsoft introduced TEXTSPLIT, a dynamic array function capable of splitting text strings horizontally across columns, vertically across rows, or simultaneously across both dimensions.

Syntax and Parameter Overview of TEXTSPLIT

The core syntax of TEXTSPLIT is structured as follows:

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

Parameter Breakdown:

• text: The raw text string or cell reference you wish to parse.
• col_delimiter: The character or string used to split text horizontally into adjacent columns (e.g., "," or ";").
• [row_delimiter]: The character or string used to split text vertically down separate rows.
• [ignore_empty]: TRUE to skip consecutive delimiters and prevent blank cells; FALSE (default) to preserve blanks.
• [match_mode]: 0 for case-sensitive delimiter matching (default); 1 for case-insensitive matching.
• [pad_with]: The fallback value to populate uneven matrix cells (defaults to #N/A).

1. Splitting Text Horizontally Across Columns

To split a full name or delimited address into separate columns, pass only the col_delimiter:

=TEXTSPLIT(A2, " ")

If cell A2 contains “Sarah Jane Connor”, the formula automatically spills across three horizontal cells: Sarah (Column B), Jane (Column C), and Connor (Column D).

2. Splitting Text Vertically Down Rows

To split a comma-separated list into a clean vertical roster, omit the col_delimiter (leaving an empty argument) and pass the comma to the row_delimiter parameter:

=TEXTSPLIT(A2, , ", ")

If cell A2 contains “Apples, Oranges, Bananas, Grapes”, the formula outputs each fruit down its own individual row in the spreadsheet.

3. Parsing Two-Dimensional Matrices (Splitting Across Rows and Columns)

The true power of TEXTSPLIT is revealed when parsing structured key-value pairs or multi-item order logs. Consider a single cell containing raw transaction data formatted like this:

Widget-10;Gadget-25;Device-40

Here, the semicolon (;) separates distinct items (rows), while the hyphen (-) separates the product name from its quantity (columns). You can parse this entire string into a structured 2-column table in a single step:

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

Excel immediately constructs a 3-row by 2-column matrix:

• Row 1: Widget | 10
• Row 2: Gadget | 25
• Row 3: Device | 40

4. Handling Multiple Alternative Delimiters

Real-world datasets frequently contain inconsistent delimiters—such as text separated by a mix of commas, slashes, and semicolons. You can supply an array constant of delimiters inside curly braces ({}):

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

Excel evaluates all listed delimiters concurrently, splitting the text cleanly regardless of which separator was entered.

Comparison: Legacy Text to Columns vs. TEXTSPLIT

Feature	Text to Columns Wizard	TEXTSPLIT Function
Recalculation	Static (Requires manual re-run)	Dynamic (Instant formula update)
Vertical Splitting (Rows)	No (Columns only)	Yes (Native row delimiter)
2D Matrix Generation	No	Yes (Both row and column split)
Data Overwrite Risk	High (Silently overwrites cells)	Safe (Throws #SPILL! if obstructed)