How to Use the TEXTSPLIT Function with Multiple Delimiters in Microsoft Excel

Cleaning messy data exports, address fields, and concatenated system logs in Microsoft Excel frequently requires splitting text strings into discrete columns and rows. Historically, analysts relied on the legacy “Text to Columns” wizard or constructed cumbersome formulas combining FIND, MID, LEN, and SUBSTITUTE. The modern TEXTSPLIT function in Microsoft Excel provides a powerful dynamic array solution, capable of parsing strings across both columns and rows simultaneously while supporting multiple disparate delimiters in a single formula.

Understanding the TEXTSPLIT Syntax and Arguments

The TEXTSPLIT function takes up to six arguments:

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
  • text: The text string or cell reference you wish to split.
  • col_delimiter: One or more characters used to divide text horizontally into columns.
  • row_delimiter: Optional. One or more characters used to divide text vertically into rows.
  • ignore_empty: Optional boolean. Enter TRUE to ignore consecutive delimiters and prevent empty spill cells (defaults to FALSE).
  • match_mode: Optional. 0 for case-sensitive matching (default); 1 for case-insensitive matching.
  • pad_with: Optional. The value used to populate missing cells when a two-dimensional grid is irregular (defaults to #N/A).

Splitting Columns Using Multiple Delimiters

Unstructured data frequently contains inconsistent separators—such as a mixture of commas, semicolons, hyphens, and slashes. To specify multiple delimiters in TEXTSPLIT, enclose them within an array constant using curly braces {"...", "..."}.

Example: Parsing Addresses with Mixed Separators

Suppose cell A2 contains the string:

Suite 400; 120 London Wall, London - EC2Y 5HN / United Kingdom

Notice that the separators include semicolons (; ), commas (, ), hyphens ( - ), and forward slashes ( / ). To split this string cleanly across individual columns:

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

The formula evaluates each delimiter alternative and spills the result into five distinct horizontal cells:

  • Cell 1: Suite 400
  • Cell 2: 120 London Wall
  • Cell 3: London
  • Cell 4: EC2Y 5HN
  • Cell 5: United Kingdom

Splitting Text Simultaneously Across Rows and Columns

When processing key-value pairs or structured records stored within a single text cell, you can supply both a column delimiter and a row delimiter to construct an instant two-dimensional table.

Example: Converting Key-Value Strings into a Table

Suppose cell A2 contains product inventory records separated by semicolons, with key-value pairs separated by equal signs:

SKU=A101;Product=Keyboard;Price=45.00;Stock=120

To split the keys and values into columns while placing each attribute on its own row:

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

Here:

  • "=" is the column delimiter, separating the attribute name from its value.
  • ";" is the row delimiter, creating a new table row for each pair.

The formula generates a tidy 4-row by 2-column spill grid in real time without refreshing.

Handling Consecutive Delimiters with ignore_empty

When text exports contain duplicate separators (such as double commas or irregular spacing), TEXTSPLIT by default outputs blank cells for adjacent delimiters. To collapse redundant delimiters automatically, set the fourth argument to TRUE:

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

This ensures that contiguous spaces or separator chains do not introduce empty columns into your spreadsheet.

Handling Asymmetric Arrays with pad_with

When splitting multi-row data where some rows contain fewer elements than others, Excel outputs a #N/A error in missing cell spaces. To maintain clean report formatting, supply a custom placeholder string using the pad_with argument:

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

Any uneven rows will display a clean hyphen (-) instead of an unsightly error tag.

Best Practices and Integration with Other Dynamic Functions

  • Trim Whitespace Automatically: Pair TEXTSPLIT with TRIM (e.g., =TRIM(TEXTSPLIT(A2, ","))) to ensure leading and trailing whitespace is stripped from extracted tokens.
  • Combine with TOROW or TOCOL: If a multi-row split needs to be reshuffled into a single continuous vertical column, nest the formula inside TOCOL(TEXTSPLIT(A2, ",")).
  • Formulas in Formulas: The spilled results of TEXTSPLIT can be directly passed into aggregation functions such as SUM, VLOOKUP, or FILTER.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.