Excel Function: TEXTSPLIT

The TEXTSPLIT function splits a text into an array using column and row separators.


=TEXTSPLIT(text, column_delimiter)


=TEXTSPLIT(text, column_delimiter, row_delimiter, ignore_empty, case_sensitive, fill_with)

Example of use

The goal here is to use a single TEXTSPLIT function per row to complete each column of the table:

excel function textsplit

Enter the TEXTSPLIT function:

In most cases, you will only need to fill in text and column_delimiter:

excel function textsplit divide

Retrieve a single value

The TEXTSPLIT function returns an array of values and the INDEX function returns one of the values from an array. Thus, the combination of the 2 functions allows you to return a single value.

In this example, to return only the middle value, enter:

excel function textsplit index
If needed, you can download the Excel file used here: textsplit.xlsx
Note: this function is only available with Office 365.