Splits the given text string by columns and rows based on the specified delimiter.
Syntax
TEXTSPLIT(text; col_delimiter; [row_delimiter]; [ignore_empty]; [mode]; [fill_with])
text: The text string given as input. Eg., A2
col_delimiter: The text by which the given string is split across the adjacent columns. Eg., "@"
row_delimiter: The text by which the given string is split to the rows below. Eg., ";"
ignore_empty: Specifies whether to create an empty cell when two consecutive delimiters are present. Defaults to TRUE if omitted.
TRUE - Ignores the empty value and does not create empty cells
FALSE - Creates empty cells when two or more delimiters are present consecutively in the given string.
mode: Specifies whether the search must be case-sensitive. Defaults to 0 if omitted.
0 - Case sensitive
1 - Case insensitive
fill_with: A value to use when one or more text values are missing in the result. Defaults to #N/A if omitted. Eg., "-"
Remarks
Multiple delimiters can also be used in the function in the form of an array. For example, =TEXTSPLIT(A1:A5; {"-";","}) splits the string by both hyphen (-) and comma (,).
Examples
Formula |
=TEXTSPLIT(A2;"-") |
=TEXTSPLIT(A3;{"-";";"}) |
=TEXTSPLIT(A4;"@";".";;;"-") |
Get a hands-on experience of the function in the embedded range below.
Possible Errors
Errors | Meaning |
#N/A! | |
#NAME! | The function name is incorrect or invalid. The given defined name (if any) is invalid. There is a typo in the defined name used in the function. Double quotes are missing for text values in the function. Colon missing in a cell range reference.
|
#VALUE! | |
#REF! | |
Similar Functions