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])


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.

Optional. Predefined integer to determine case-sensitivity at delimiter matching.

  • 0: Case-sensitive (default)
  • 1: Not case-sensitive
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

Excel TEXTSPLIT Function - Default

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--

Download Workbook


  • 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.