Excel Function: TEXTSPLIT

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

Usage:

=TEXTSPLIT(text, column_delimiter)

or

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

Enter the TEXTSPLIT function:

• text: the data to split
• column_delimiter: the column delimiter
• row_delimiter: the row delimiter (optional)
• ignore_empty: 1 (default) = empty fragments are ignored, 0 = empty fragments are kept (empty cells)
• case_sensitive: 0 (default) = respect the case (upper / lower case), 1 = ignore the case
• fill_with: value with which the result should be filled (default #N/A)

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

=TEXTSPLIT(A2,";")

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:

=INDEX(TEXTSPLIT(A2,";"),1,2)