The TEXTSPLIT function in Excel 365 helps split text dynamically based on delimiters, saving time and effort. The TEXTSPLIT function has been added to Excel 365 from version 2208 or Excel Online.
Explanation of the TEXTSPLIT function
The TEXTSPLIT function splits text based on a delimiter. It requires two arguments to work properly:
- The text in the cell splits.
- The delimiter for splitting text into columns.
- The delimiter for splitting text into rows.
=TEXTSPLIT(text,delimiter_column,delimiter_row)
For example, consider a list of names and addresses stored in a single column.
In the past, splitting text based on a delimiter in Excel often required the Text to Columns tool. While this tool works well, it does not automatically update when your source data changes.
TEXTSPLIT offers a more dynamic solution 😃. It recalculates the split results automatically whenever the source data is updated. In this case, each part of the addresses is separated by commas ( , ), which makes them easy to split. You can write the formula as follows:
Important Note: The result of TEXTSPLIT is a formula, not plain text. To convert it into static text, use Copy and then Paste as Values
TEXTSPLIT as argument
As you may know, Excel 365 can return an array instead of a single cell. So, in certain circumstances, TEXTSPLIT can be used as an argument in other functions.
For instance, you can combine the TEXTSPLIT with COUNTA to count the number of words in a text. Here, the delimiter between the words is a space character.
You can also combine the TEXTSPLIT function with the INDEX function as its first argument.
For example, each part of the tracking number has a specific meaning in this situation. Using the TEXTSPLIT function, you can easily separate each part on the dash ( - ).
However, instead of extracting all the parts, you can choose to extract only one field. For instance, the row number is the forth part of the tracking number. You can extract this specific information directly with the following formula:
Try the TEXTSPLIT function in Excel 365 today to handle text manipulation easily and efficiently!
