Excel Function: TEXTSPLIT

Purpose

The TEXTSPLIT function splits text values into parts and places each one into a different cell. A part of a text value lies between characters which delimit its boundaries. Cells populated by TEXTSPLIT can span multiple rows and columns, form a rectangular grid, and with the exception of padded cells will always contain text values. For example, if TEXTSPLIT were to operate on the text value “x,y,z” and treat comma (,) as a delimiter then 3 parts would be produced: “x“, “y“, and “z“.

Invocations of the TEXTSPLIT function take the form:

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

Example

Consider a cell located on row 1 & column 1 of a worksheet containing the formula:

=TEXTSPLIT("a=1,b=2,c","=",",")

The formula operates on the text value “a=1,b=2,c” and designates the equals sign (=) as a column delimiter and comma (,) as a row delimiter. TEXTSPLIT reads the text value from left to right and designates sequences of characters bounded by delimiters as parts. TEXTSPLIT then uses the delimiter on the left-hand side of a part to decide which cell to place it in. TEXTSPLIT splits “a=1,b=2,c” into parts by performing the 6 steps illustrated below:

Arguments

Argument	Argument type	Description
text	Mandatory	A text value to be split into parts.
col_delimiter	Mandatory	A column delimiter is a sequence of 1 or more characters whose presence marks the boundary between 2 parts of a text value. For example, in the text value “a|b”, the bar character (|) marks the boundary between “a” and “b”. TEXTSPLIT places a part of a text value appearing to the right of a column delimiter in the cell to the right of the preceding part. Column delimiters can be defined in one of two ways: Type Description Single delimiter 1 or more characters defining the boundary between parts. Multiple delimiters If more than 1 column delimiter exists in a text value, they can be passed to the column_delimiter argument in an array constant. For example, {“,” , “|”} defines both the comma (,) and bar (|) characters as column delimiters.
row_delimiter	Optional	A row delimiter is a sequence of 1 or more characters whose presence marks the boundary between 2 parts of a text value. For example, in the text value “a|b”, the bar character (|) marks the boundary between “a” and “b”. A part of a text value appearing to the right of a row delimiter is placed in the row below the one containing the preceding part, and underneath the cell containing the formula cell referencing TEXTSPLIT. Row delimiters can be defined in one of two ways: Type Description Single delimiter 1 or more characters defining the boundary between parts. Multiple delimiters If more than 1 row delimiter exists in a text value, they can be passed to the row_delimiter argument in an array constant. For example, {“,” , “|”} defines both the comma (,) and bar (|) characters as row delimiters.
ignore_empty	Optional. Defaults to FALSE if omitted.	The absence of a value between 2 delimiters represents a missing part. The ignore_empty argument determines how TEXTSPLIT manages these situations. It can assume one of two values: Value Description TRUE TEXTSPLIT does not create an empty cell to represent a missing part. Its behaviour differs based on the types of delimiters bounding a missing part. See example 3. FALSE TEXTSPLIT creates an empty cell to represent a missing part. Its behaviour differs based on the types of delimiters bounding a missing part. See example 3.
match_mode	Optional. Defaults to 0 if omitted.	Determines if TEXTSPLIT performs a case-sensitive match between (1) values passed to the row_delimiter & column_delimiter arguments and (2) the value passed to the text argument. Value Description 0 Case-sensitive match. 1 Case insensitive.
pad_with	Optional. Defaults to #N/A if omitted.	In some situations, cells which are not the recipient of a part derived from the text argument must be co-opted and populated with a value by TEXTSPLIT so its output can form a rectangular grid. The value supplied to the pad_with argument can be of type error, number, text or logical and is the value placed into co-opted cells. Values in co-opted cells are of the same type as the value supplied to the pad_with argument.

Related Functions

Function	Example	Purpose

Error Conditions

Error value	Condition type	Condition
#SPILL!	Runtime	One or more of the cells populated by TEXTSPLIT already contain a value.

Examples: Defining Column Delimiters

Example	Formula	Result	Description
1	=TEXTSPLIT("a|b|c|d","|")		Extract 4 values delimited by the vertical bar (|) character. The vertical bar (|) character is defined as a column delimiter. TEXTSPLIT populates 4 cells on the same row.
2	=TEXTSPLIT("a|b?c?d",{"|","?"})		Extract 4 values delimited by the vertical bar (|) and question mark (?) characters. Vertical bar (|) and question mark (?) are defined as column delimiters. TEXTSPLIT populates 4 cells on the same row.

Examples: Defining Row Delimiters

Example	Formula	Result	Description
1	=TEXTSPLIT("a|b|c|d",,"|")		Extract 4 values delimited by the vertical bar (|) character. No column delimiters are defined. The vertical bar (|) character is defined as a row delimiter. TEXTSPLIT populates 4 cells in the same column.
2	=TEXTSPLIT("a|b?c?d",,{"|","?"})		Extract 4 values delimited by the vertical bar (|) and question mark (?) characters. No column delimiters are defined. Vertical bar (|) and question mark (?) are defined as row delimiters. TEXTSPLIT populates 4 cells in the same column.

Examples: Managing Missing Parts

Example	Formula	Result	Description
1	=TEXTSPLIT("a|b||d",,"|",FALSE)		Extract values from “a|b||d“. ignore_empty is set to FALSE. No column delimiters are defined. The vertical bar (|) character is defined as a row delimiter. A part is missing between 2 consecutive row delimiters (||). TEXTSPLIT populates 4 cells, 1 of which is blank.
2	=TEXTSPLIT("a|b||d",,"|",TRUE)		Extract values from “a|b||d“. ignore_empty is set to TRUE. No column delimiters are defined. The vertical bar (|) character is defined as a row delimiter. A part is missing between 2 consecutive row delimiters (||). TEXTSPLIT populates 4 cells, 1 of which is blank.
3	=TEXTSPLIT("a|b||d","|",,FALSE)		Extract values from “a|b||d“. ignore_empty is set to FALSE. No row delimiters are defined. The vertical bar (|) character is defined as a column delimiter. A part is missing between 2 consecutive column delimiters (||). TEXTSPLIT populates 4 cells, 1 of which is blank.
4	=TEXTSPLIT("a|b||d","|",,TRUE)		Extract values from “a|b||d“. ignore_empty is set to TRUE. No row delimiters are defined. The vertical bar (|) character is defined as a column delimiter. A part is missing between 2 consecutive column delimiters (||). TEXTSPLIT ignores the missing value and populates 3 cells.
5	=TEXTSPLIT("a|b|?d","|","?",FALSE)		Extract values from “a|b|?d“. ignore_empty is set to FALSE. The vertical bar (|) character is defined as a column delimiter. The question mark (?) character is defined as a row delimiter. A part is missing between a column and a row delimiter. TEXTSPLIT populates 6 cells, 1 is blank and 2 are padded with #N/A.
6	=TEXTSPLIT("a|b|?d","|","?",TRUE)		Extract values from “a|b|?d“. ignore_empty is set to TRUE. The vertical bar (|) character is defined as a column delimiter. The question mark (?) character is defined as a row delimiter. A part is missing between a column and a row delimiter. TEXTSPLIT populates 4 cells, 1 is padded with #N/A.
7	=TEXTSPLIT("a|b?|d","|","?",FALSE)		Extract values from “a|b?|d“. ignore_empty is set to FALSE. The vertical bar (|) character is defined as a column delimiter. The question mark (?) character is defined as a row delimiter. A part is missing between a row and a column delimiter. TEXTSPLIT populates 4 cells, 1 of which is blank.
8	=TEXTSPLIT("a|b?|d","|","?",TRUE)		Extract values from “a|b?|d“. ignore_empty is set to TRUE. The vertical bar (|) character is defined as a column delimiter. The question mark (?) character is defined as a row delimiter. A part is missing between a row and a column delimiter. TEXTSPLIT populates 4 cells, 1 is padded with #N/A.

Examples: Matching Delimiters

Example	Formula	Result	Description
1	=TEXTSPLIT("aXYb",,"xy",,1)		Extract values from “aXYb“. match_mode is set to 1. The lower-case sequence of characters “xy” is defined as a row delimiter. “xy” is matched to “XY” in “aXYb“. TEXTSPLIT populates 2 cells in the same column.
2	=TEXTSPLIT("aXYb",,"xy",,0)		Extract values from “aXYb“. match_mode is set to 0. The lower-case sequence of characters “xy” is defined as a row delimiter. “xy” cannot be matched to “XY” in “aXYb“. TEXTSPLIT fails to extract any values from “aXYb“.

Examples: Padding Missing Parts

Example	Formula	Result	Description
1	=TEXTSPLIT("a=1?b","=","?",,,10)		A part is missing in the value passed to the text argument, following “b”. The number “10” is passed to the pad_with argument. The question mark (?) character is defined as a row delimiter. The equals sign (=) is defined as a column delimiter. TEXTSPLIT populates 4 cells, the missing part is replaced with “10”.
2	=TEXTSPLIT("a=1,b","=",",",,,"LOST")		A part is missing in the value passed to the text argument, following “b”. The text “LOST” is passed to the pad_with argument. The comma (,) character is defined as a row delimiter. The equals sign (=) is defined as a column delimiter. TEXTSPLIT populates 4 cells, the missing part is replaced with “LOST”.
3	=TEXTSPLIT("a=1,b","=",",",,,FALSE)		A part is missing in the value passed to the text argument, following “b”. The logical value “FALSE” is passed to the pad_with argument. The comma (,) character is defined as a row delimiter. The equals sign (=) is defined as a column delimiter. TEXTSPLIT populates 4 cells, the missing part is replaced with “FALSE”.
4	=TEXTSPLIT("a=1,b","=",",",,,#VALUE!)		A part is missing in the value passed to the text argument, following “b”. The error value “#VALUE!” is passed to the pad_with argument. The comma (,) character is defined as a row delimiter. The equals sign (=) is defined as a column delimiter. TEXTSPLIT populates 4 cells, the missing part is replaced with “#VALUE!”.