TEXTSPLIT takes five arguments, only two of which are required. The first argument, text, is the text string to split. The second argument, col_delimiter is the delimiter to use for splitting text into separate columns. The third argument, row_delimiter, is the delimiter to use for splitting text into separate rows. Either col_delimiter or row_delimiter must be supplied along with text. The fourth argument, ignore_empty, controls TEXTSPLIT’s behavior with empty values (i.e. when delimiters appear together with no value in between). By default, ignore_empty is FALSE, which means 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. The fifth argument is match_mode, which 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 is pad_with, which is a value that will appear when the output from TEXTSPLIT is a 2D array and a value is missing. See below for more information. 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: 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: 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: 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: 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:
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: 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: In cell E7, “x” is supplied for pad_with so “x” appears in cell F8 instead of #N/A.
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: 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: Notice the extra space that appears before purple in cell I3 is gone in cell I5.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
