# Split your text on a delimiter – TEXTSPLIT Function

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.

We can easily split the cell on the comma delimiter.

=TEXTSPLIT(A2,",")

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

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

## 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,,",")

# Split your text on a delimiter – TEXTSPLIT Function

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.

We can easily split the cell on the comma delimiter.

=TEXTSPLIT(A2,",")

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

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

## 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,,",")