TEXTSPLIT function in Excel

Use the TEXTSPLIT function in Excel 365 to split text into rows or columns using delimiters.

This guide teaches you how to use the TEXTSPLIT function, starting with simple text splits and moving on to more advanced formulas.

Basic TEXTSPLIT function

When TEXTSPLIT has only 2 arguments, it splits text into columns. In this example, we're using a comma and a space as the delimiter (second argument).

Note: the TEXTSPLIT function, entered into cell B2, fills multiple cells (B2 and C2). Wow! This behavior is called spilling.

Split Text into Columns or Rows

The TEXTSPLIT function below, with 2 arguments, splits text into columns using a dash as the delimiter. The results spill over into the cells to the right of cell B1.

Use 3 arguments and leave the second argument empty to split text into rows!

Multiple Delimiters

The TEXTSPLIT function below splits cell A1 using multiple delimiters (semicolon, dash, and slash).

Explanation: you can split text using various delimiters by providing them as an array constant to TEXTSPLIT.

Ignore Empty Values

You can use the 4th argument (set it to TRUE) of the TEXTSPLIT function to ignore empty values.

Let's say you have a string containing names separated by commas, and you want to split this string into separate cells in Excel while ignoring any empty values.

1. Without using the 4th argument, TEXTSPLIT returns empty cells.

2. The TEXTSPLIT function below ignores empty values resulting from consecutive commas.

Tip: if your TEXTSPLIT function returns leading spaces, extra spaces or trailing spaces in cells that you want removed, you can add a TRIM function to remove these spaces.

Case-Insensitive Splitting

By default, TEXTSPLIT is case-sensitive when searching for a delimiter. To make the delimiter case-insensitive, use the 5th argument (set it to 1).

Suppose you have a list of dimensions with delimiters that vary in case, such as "by", "By" and "BY".

1. The TEXTSPLIT function below doesn't recognize " By " or " BY ".

2. To handle all variations of "by" regardless of case, set the 5th argument to 1.

Explanation: this TEXTSPLIT function is case-insensitive, splitting text strings using any case variation of "by", including "By" and "BY". Note that this configuration leaves the 3rd and 4th arguments empty.

Two-dimensional Split

Imagine you have a multiline text string (see cell A1 below) where each line represents a person's name and their age, separated by an equal sign, and each person separated by a comma and a space.

You want to split this text into columns for names and ages and rows for each person. Here's how you can achieve this using TEXTSPLIT.

Explanation: this formula splits the text string based on the equal sign "=" as the column delimiter (2nd argument) and the comma and space ", " as the row delimiter (3rd argument).

If you're new here, welcome to Excel Easy! Join over 1 million monthly Excel learners. You can find popular courses here: Excel Functions and Data Analysis in Excel.