Split your text on a delimiter – TEXTSPLIT Function

Split your text on a delimiter – TEXTSPLIT Function
Last Updated on 20/04/2024
Reading time: 2 minutes

The TEXTSPLIT function has been added to Excel 365 from version 2208 or Excel Online. This function splits the content of a cell based on a delimiter.

The TEXTSPLIT function of Excel needs 3 arguments

  1. The text to split

    Just select the cell with the text you want to split

  2. Write the delimiter to split the result in row

    Yes, this function can manage 2 delimiters (one for the row, one for the column)

  3. Delimiter for the column

    If you want to return the result in the column, this is the argument you must fill in.

Explanation of the TEXTSPLIT function

As the name suggests, this function will split text according to a delimiter. Two parameters are mandatory:

  • The text in a cell
  • The delimiter (result in columns)
  • The delimiter (result in rows)

For example, here we have here a list of contacts and their addresses.

List Name and Address

We can easily split the cell on the comma delimiter.

=TEXTSPLIT(A2,",")

The TEXTSPLIT function splits your text on a delimiter

As you can see, using this function is extremely simple to use. As a reminder, the formula that allowed us to obtain the same result was written with the LAMBDA function and is very complex to write.

Count the number of words.

You can also associate the TEXTSPLIT function with other functions to return other information. For instance, to count the number of words in the addresses, we will write the following formula

=COUNTA(TEXTSPLIT(D2," "))

This time, the delimiter is not a comma but a space

Count words in your cells

Extract one item from the result

Now, if you want to extract a single value from the list, the INDEX function will be beneficial. For example, the city is always the fourth item of the original cell. To extract only the city, you will write the following formula:

=INDEX(TEXTSPLIT(A2,", "),4)

Extract a specific element with the INDEX function

Return the result in rows

In you want to return the result in rows, you simply have to fill the 3rd argument of the TEXTSPLIT function instead of the second.

=TEXTSPLIT(A2,,",")

TEXTSPLIT return the result in rows

Leave a Reply

Your email address will not be published. Required fields are marked *

Split your text on a delimiter – TEXTSPLIT Function

Reading time: 2 minutes
Last Updated on 20/04/2024

The TEXTSPLIT function has been added to Excel 365 from version 2208 or Excel Online. This function splits the content of a cell based on a delimiter.

The TEXTSPLIT function of Excel needs 3 arguments

  1. The text to split

    Just select the cell with the text you want to split

  2. Write the delimiter to split the result in row

    Yes, this function can manage 2 delimiters (one for the row, one for the column)

  3. Delimiter for the column

    If you want to return the result in the column, this is the argument you must fill in.

Explanation of the TEXTSPLIT function

As the name suggests, this function will split text according to a delimiter. Two parameters are mandatory:

  • The text in a cell
  • The delimiter (result in columns)
  • The delimiter (result in rows)

For example, here we have here a list of contacts and their addresses.

List Name and Address

We can easily split the cell on the comma delimiter.

=TEXTSPLIT(A2,",")

The TEXTSPLIT function splits your text on a delimiter

As you can see, using this function is extremely simple to use. As a reminder, the formula that allowed us to obtain the same result was written with the LAMBDA function and is very complex to write.

Count the number of words.

You can also associate the TEXTSPLIT function with other functions to return other information. For instance, to count the number of words in the addresses, we will write the following formula

=COUNTA(TEXTSPLIT(D2," "))

This time, the delimiter is not a comma but a space

Count words in your cells

Extract one item from the result

Now, if you want to extract a single value from the list, the INDEX function will be beneficial. For example, the city is always the fourth item of the original cell. To extract only the city, you will write the following formula:

=INDEX(TEXTSPLIT(A2,", "),4)

Extract a specific element with the INDEX function

Return the result in rows

In you want to return the result in rows, you simply have to fill the 3rd argument of the TEXTSPLIT function instead of the second.

=TEXTSPLIT(A2,,",")

TEXTSPLIT return the result in rows

Leave a Reply

Your email address will not be published. Required fields are marked *