The TEXTSPLIT function is a Text function that returns an array of substrings split by a given delimiter. If you use the function itself, Excel spills the substrings into multiple cells as a regular dynamic array. In this guide, we’re going to show you how to use the TEXTSPLIT function and go over some tips and error-handling methods.
Supported versions
- Excel 365 (Office Insider Beta only)
TEXTSPLIT Function Syntax
TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])
Arguments
| text | The text you want to split. |
| col_delimiter | The text to delimit and spill the text across columns. |
| row_delimiter | Optional. The text to delimit and spill the text across rows. |
| ignore_empty | Optional. A Boolean value to ignore or not the empty values. The default is TRUE. |
| match_mode |
Optional. Predefined integer to determine case-sensitivity at delimiter matching.
|
| pad_with | Optional. Value to pad missing values in 2D arrays. The default is #N/A. |
TEXTSPLIT Function Examples
Default Scenario
By default, the function generates that can be spilled into columns. Thus, the required arguments are the text you want to split (text) and the column delimiter (col_delimiter).
=TEXTSPLIT("Bartholomew Henry Allen"," ") //fills 3 cells in the same row with each string
Multiple delimiters
If your source data contains multiple delimiters, you can enter multiple delimiters as an array value.
=TEXTSPLIT("Death|Neutral-Wanda Maximoff|Good",{"-","|"}) // splits texts for both dash (-) and pipe (|) characters
Splitting into rows
If you need to populate the split strings through to rows, omit the col_delimiter argument and enter the delimiter as row_delimiter.
=TEXTSPLIT("Wanda Maximoff",," ") // fills two cells in the same column
2D array
If you supply both the col_delimiter and the row_delimiter arguments, Excel spills the text values as 2D array:
=TEXTSPLIT(<text>,"-","|") // "|" splits rows, "-" splits columns
Ignore or include empty values
The ignore_empty argument allows you to determine to ignore or not the consecutive delimiters without a value between them.
If the argument is TRUE, Excel skip cells for each consecutive delimiter.
=TEXTSPLIT("The Flash++Male+Human","+",,FALSE) // populates 4 cells due to consecutive pluses (++)
Case-sensitivity at delimiter
By default, the TEXTSPLIT function applies a case-sensitive search to find the delimiters. You can make the delimiter search not case-sensitive by giving "1" to the match_mode parameter.
=TEXTSPLIT("4 FT 0"," ft ",,,0) // Doesn't split the given string
=TEXTSPLIT("4 FT 0"," ft ",,,1) // Splits the string into 2.
Padding for 2D arrays
The final argument of the TEXTSPLIT function is the pad_width. This argument determines the replacement value to fill an empty cell when a value is missing in a 2D array output. If the output is a 2D array and a value is missing, the function returns a value to keep the structure intact. The default value of the argument is #N/A.
=TEXTSPLIT(C62,"-","|",TRUE) // pads empty cells with #N/A =TEXTSPLIT(C70,"-","|",TRUE,,"--No data!--") // pads empty cells with --No Data--
Remarks
- If the TEXTSPLIT function is not available in your Excel, prefer other options: How to split text in Excel in 5 ways
- Microsoft states that this function is the inverse of the TEXTJOIN function.
- If all you need is the single part before or after the delimiter, you can prefer TEXTBEFORE and TEXTAFTER functions return a single value.
