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 function TEXTSPLIT
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 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
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)
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,,",")
