TEXTSPLIT Function

The TEXTSPLIT function splits a text string with a given delimiter into multiple values. The output from TEXTSPLIT is an array that will spill into multiple cells in the workbook. TEXTSPLIT can split a text string into rows or columns. The generic syntax for TEXTSPLIT looks like this:

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

Text is the text string to split. Col_delimiter is the delimiter to use for splitting text into columns, and row_delimiter is the delimiter to use for splitting text into rows. The fourth argument, ignore_empty, controls TEXTSPLIT's behavior with empty values (i.e. no value between delimiters). By default, ignore_empty is FALSE and TEXTSPLIT will not ignore empty values. In practice, this means you will see an empty cell on the worksheet when there is an empty value in text. Set ignore_empty to TRUE to ignore empty values. Match_mode determines case sensitivity when looking for a delimiter. By default, TEXTSPLIT is case-sensitive and match_mode is zero (0). Supply 1 to disable case sensitivity. The last argument, pad_with, is the value to return when the output from TEXTSPLIT is a 2D array and value(s) are missing. See below for more information.

Note Excel has three functions that will split a text value: TEXTBEFORE, TEXTAFTER, and TEXTSPLIT. Use TEXTBEFORE to extract text before a delimiter, TEXTAFTER to extract text after a delimiter, and TEXTSPLIT to extract all text separated by delimiters.

Basic usage

The result from TEXTSPLIT is an array that can be horizontal (columns) or vertical (rows). The worksheet below shows both options:

The first formula in cell D3 separates the three values into separate columns:

=TEXTSPLIT(B3,",") // returns {"Red","Blue","Green"}

Notice a comma without space is used as the col_delimiter and enclosed in double quotes (","). The formula in cell D5 uses the same delimiter to split the text into separate rows:

=TEXTSPLIT(B3,,",") // returns {"Red";"Blue";"Green"}

In the second formula, the delimiter (",") appears as the third argument, row_delimiter, and col_delimiter is not provided.

Ignoring empty values

By default, TEXTSPLIT will not ignore empty values in the text, where empty values are defined as two or more consecutive delimiters without a value in between. This behavior is controlled by the ignore_empty argument, which defaults to FALSE, as you can see in the worksheet below:

The formula in cell D3 does not include a value for ignore_empty, which defaults to FALSE:

=TEXTSPLIT(B3,",") // empty values not ignored

Notice empty values are not ignored. TEXTSPLIT includes an empty cell in the output where the empty value appeared in text. In the second formula, ignore_empty is set to TRUE, so the missing value between Red and Green is ignored completely:

=TEXTSPLIT(B3,",",,TRUE) // ignore empty values

In this case, TEXTSPLIT behaves as if the missing value does not exist at all.

Note: you can use 1 and 0 in place of TRUE and FALSE for the ignore_empty argument.

Match mode

The fifth argument, match_mode, determines case sensitivity when looking for a delimiter. By default, TEXTSPLIT is case-sensitive and match_mode is zero (0). Supply 1 to disable case sensitivity. In the example below the delimiter is " x " and " X ". The formula in D5 sets match mode to 1 to make TEXTSPLIT ignore case. As a result, the formula works for both cases:

=TEXTSPLIT(B5," x ",,,1)

Rows and columns

TEXTSPLIT can split text into rows and columns at the same time, as seen below:

In this case, an equal sign ("=") is provided as col_delimiter and a comma (",") is provided as row_delimiter:

=TEXTSPLIT(B3,"=",",")

The resulting array from TEXTSPLIT contains 3 rows and 2 columns.

Padding

The last argument in TEXTSPLIT is pad_with. This argument is optional and will default to #N/A. Padding is used when the output contains rows and columns and a value is missing that would affect the structure of the array. In the worksheet below, "Blue" does not contain a quantity (there is no "=" delimiter). As a result, TEXTSPLIT returns #N/A where the quantity would go, to maintain the integrity of the array.

The formula in cell E3 contains does not specify a pad_with argument so the default value is returned:

=TEXTSPLIT(B3,"=",",") // default padding is #N/A

In cell E7, "x" is supplied for pad_with so "x" appears in cell F8 instead of #N/A.

=TEXTSPLIT(B3,"=",",",,"x")

Multiple delimiters

Multiple delimiters can be supplied to TEXTSPLIT as an array constant like {"x","y"} where x and y represent delimiters:

In the worksheet above, the text in B3 is delimited by both hyphens "-" and commas (","). The formula in cell F3 is:

=TEXTSPLIT(B3,{"-",","})

Notice also that there is an extra space separating green and purple. The TRIM function can be used to clean up extra space characters that appear in the output from TEXTSPLIT. The formula in F5 is:

=TRIM(TEXTSPLIT(B3,{"-",","}))

Notice the extra space that appears before purple in cell I3 is gone in cell I5.

Array of arrays

When using TEXTSPLIT, you might run into a limitation of the Excel formula engine where the formula will not return "arrays of arrays". When TEXTSPLIT is used on a single cell, it returns the text in a single array, and values spill onto the worksheet into multiple cells. However, when TEXTSPLIT is used on a range of cells, TEXTSPLIT returns an "array of arrays". The result may be a truncated version of the data or in some cases an error. Example here.