Split your text on a delimiter – TEXTSPLIT Function

Split your text on a delimiter – TEXTSPLIT Function

Last Updated on 15/08/2023
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.

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 function to return other informations.

For instance, to count the number of word in the addresses, we will write the following formula

=COUNTA(TEXTSPLIT(D2," "))

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

Count the words with TEXTSPLIT

Extract one item from the result

Now, if you want to extract a single value from the list, the INDEX function will be extremely helpful

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 15/08/2023

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.

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 function to return other informations.

For instance, to count the number of word in the addresses, we will write the following formula

=COUNTA(TEXTSPLIT(D2," "))

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

Count the words with TEXTSPLIT

Extract one item from the result

Now, if you want to extract a single value from the list, the INDEX function will be extremely helpful

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 *