Function Reference: TEXTSPLIT
| Syntax | ||
| =TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pad_with]) | ||
| Purpose / Description | ||
| Returns an array containing the sub-sparts of the text string, divided by the column and row delimiters provided. Options are provided to ignore or pad empty entries. The array will be a single row unless a row delimiter is provided and found within the text string. | ||
| Arguments | ||
| Name | Type | Description |
| text | text string | A reference or nested expression that returns a valid reference, a constant text string or a nested expression that returns a text string. Normal coercion rules apply, so if a logical value or number is passed, it will be coerced to a text string. |
| col_delimiter | text string or array of text strings | A reference or nested expression that returns a valid reference, a constant text string or a nested expression that returns a text string or array of strings that is one or more characters that delimits entries within text that should be split to new columns. An array of delimiters may be passed, such that more than one character is considered to separate sub-parts. For example {" ";",","."} would split words on a space, comma or period (full stop). Normal coercion rules apply, so if a logical value or number is passed, it will be coerced to a text string. It may not be an empty string (""). It may be omitted, but only if the optional row_delimiter is provided. |
| [row_delimiter] | text string or array of text strings | An optional reference or nested expression that returns a valid reference, a constant text string or a nested expression that returns a text string that is one or more characters that delimits entries within text that should be split to new rows. An array of delimiters may be passed, such that more than one character is considered to separate sub-parts. For example {" ";",","."} would split words on a space, comma or period (full stop) Normal coercion rules apply, so if a logical value or number is passed, it will be coerced to a text string. If omitted, entries are only split to columns based on col_delimiter. |
| [ignore_empty] | logical | A reference or nested expression that returns a valid reference, a constant text string or a nested expression that returns a logical. Normal coercion rules apply, so if a text string or number is passed, it will be coerced to a logical value. If omitted, defaults to FALSE. If set to TRUE, when there are consecutive delimiters that would lead to empty strings being returned, they are omitted. |
| [match_mode] | number | An optional reference or nested expression that returns a valid reference, a constant text string or a nested expression that returns a number. Normal coercion rules apply, so if a logical value or text string is passed, it will be coerced to a number. If the number is not an integer, it is truncated to an integer value. value must be in the range 0 ≤ n < 2. If 0 or omitted, the match for delimiters is performed on a case-sensitive basis.. If 1 then the match for delimiters is performed on a case-insensitive basis. |
| [pad_with] | any | An optional reference to a range, a constant or nested expression that provides the value to be used to pad cells where the number of columns produced in each row is not the same. Ignored when there is now row_delimited.. If omitted, the value #N/A is used to fill the newly created cells. The default value is #N/A if omitted. |
| Return Values | ||
| Type | Specific Value | Description |
| text string | If the col_delimiter and row_delimiter are not found in text, then the original text string passed to text is returned. | |
| array of text strings | An array of text strings, being the delimited sub-parts of text are returned when col_delimiter and/or row_delimiter are found within text, and the number of entries in each row or column is equal, or it is a single column or row. | |
| array of any | An array of mainly text strings, being the delimited sub-parts of text are returned when col_delimiter and/or row_delimiter are found within text, and the number of entries in each row or column is not equal, then the extra entries will be padded with pad_with, which may be of any value type. | |
| error | #VALUE! | If either col_delimiter or row_delimiter is an empty string ("") or if both col_delimiter or row_delimiter are omitted. |
| error | #NUM! | if the array referenced by the array argument is too large. |
| error | #N/A | Newly created cells may contain the value #N/A if the value of the the pad_with argument was omitted and the number of entries in each row or column is not the same in the newly created array.. |
| error | Any errors referenced by arguments are returned due to propagation of error values. | |
| Notes / Comments / Advice / Warnings | ||
| Comment: | This is an a big improvement on the clumsy way that strings had to be split in the past. It is the natural counterpart to the TEXTJOIN function. | |
| Comment: | TEXTJOIN can concatenate entries from across multiple worksheets, since it accepts multi-sheet inputs, and then TEXTSPLIT can split those to produce a simple table of summary values gathered across worksheets. | |
| Examples: | Open / download sample workbook: TEXTAFTER, TEXTBEFORE, TEXTSPLIT.xlsx | |