TEXTSPLIT

Excel Functions

Split a text string with a delimiter

Syntax

EXCEL

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

Arguments

Parameter	Description
`text`	The text string to split.
`col_delimiter`	The character(s) to delimit columns.
`row_delimiter`	[optional] The character(s) to delimit rows.
`ignore_empty`	[optional] Ignore empty values. TRUE = ignore, FALSE = preserve. Default is FALSE.
`match_mode`	[optional] Case-sensitivity. 0 = enabled, 1 = disabled. Default is 0.
`pad_with`	[optional] Value to pad missing values in 2d arrays.

Return Value

An array of split values

Details

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 columns or rows. To use TEXTSPLIT, you will need to provide the text to split and a delimiter. You can either provide a column delimiter (col_delimiter) to split text into columns, or a row delimiter (row_delimiter) to split text into rows. For example, the formula below splits the text "red-blue-green" into separate values in columns: Note that the column delimiter is provided as a hyphen ("-). If we move the hyphen ("-") to the row delimiter position, the TEXTSPLIT function will return the same values split into rows: Note that both formulas above return an array of three

Examples

Split text into columns or rows

TEXTSPLIT can split a text string into columns or rows. To use TEXTSPLIT, you will need to provide the text to split and a delimiter. You can either p

EXCEL

=TEXTSPLIT("red-blue-green","-") // returns {"red","blue","green"}

Split text into columns or rows

Note that the column delimiter is provided as a hyphen ("-). If we move the hyphen ("-") to the row delimiter position, the TEXTSPLIT function will re

EXCEL

=TEXTSPLIT("red-blue-green",,"-") // returns {"red";"blue";"green"}

Split text into columns or rows

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

EXCEL

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

Split text into columns or rows

The formula in cell D5 uses the same delimiter to split the text into separate rows:

EXCEL

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

Behavior if no delimiter is found

If the provided delimiter is not found, TEXTSPLIT will return the original text unchanged. For example, if we use TEXTSPLIT on the text string "apple

EXCEL

=TEXTSPLIT("apple orange",".") // returns "apple orange"

Ignoring empty values

The formula in cell D3 does not include a value for ignore_empty, so empty values will appear:

EXCEL

=TEXTSPLIT(B3,",") // empty values will appear

TEXTSPLIT

Syntax

Arguments

Return Value

Details

Examples

See Also