If you have lots of text in a cell in Excel and each part of the text is separated by a delimiter (space, punctuation etc), then you can use the TEXTSPLIT function to separate out each component of the text into individual cells, either across a row or column.
Download File
If you would like to follow along download the attachment below.
What is the TEXTSPLIT Function
TEXTSPLIT is a dynamic array function so behaves slightly differently to traditional Excel formulas, with its output extending across a number of cells rather than just one.
Its output is made up of text in a cell, and it splits it based on a given delimiter.
The syntax for TEXTSPLIT is:
=TEXTSPLIT(text, column_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
text: The string to split (required).
column_delimiter: The delimiter for splitting text into columns (required).
row_delimiter: The delimiter for splitting text into rows (optional).
ignore_empty: A boolean (TRUE or FALSE) indicating whether to ignore empty cells in the result (optional).
match_mode: Allows case sensitivity (0 for case-insensitive, 1 for case-sensitive). (Optional)
pad_with: Specifies the value to fill in for missing elements in the resulting array. (Optional)
Splitting Text into Columns
TEXTSPLIT works by separating out text based on delimiters. If you had a list of countries separated out by a comma, you could use the comma as the delimiter to split the text.
To use this in the function you can write A1 in the text part of the function and “,” in the delimiter part of the function.
When entering a delimiter it will need to be wrapped in quotation marks for the function to recognise it.
This function splits the text in A2 and spills it across five cells across five columns.
Understanding the dynamic array aspect of TEXTSPLIT
As TEXTSPLIT is a dynamic array function it will keep updating if further text is added after another delimiter is added to the same cell it is referencing.
Using the same function from the example above, in cell A1 you can put a comma at the end of Italy and then add Spain after the comma and TEXTSPLIT will automatically add the value Spain in column H by extending the range of the function output.
You can tell the function is a dynamic array function because when you click on a cell within it you will see a blue line surrounding the spill area of the function.
Even though the function has spilled across a range, only the first instance of the function can be edited.
This will be the value furthest to the left, but another way to know is that when clicking on the cell where the function was written, the text in the formula bar will be in black, and when you click on any of the other values the text will be in grey.
Because the function needs to spill over a range that can be made longer or shorter depending on the text value that it points to, the cells after the initial function need to be blank. If there is a value entered in a cell that the TEXTSPLIT is trying to spill into then the function will return the ‘#SPILL!’ error.
Splitting Text into Rows
If you want text from a cell to spill down a number of rows, rather than across a number of columns, TEXTSPLIT has the option to split across rows by a delimiter.
Using the same list of countries from above you can enter the delimiter in the row_delimiter segment and leave the column_delimiter segment blank.
As the function is still a dynamic array adding a new value after another delimiter in the original text will automatically extend the range of the output from the TEXTSPLIT.
Ignoring Empty Values
If there are empty values between delimiters in your text, TEXTSPLIT will allocate a spilled cell to the empty value.
There is an ignore_empty segment to the function which allows you to enter TRUE or 1 to ignore empty cells, or FALSE or 0 to include empty cells.
As including empty cells is the default if you want them included you don’t need to enter FALSE or 0.
Turn off case sensitivity when splitting by a text value
So far all of the examples used have been for a comma. If you wanted to split the text by a text value, in this case a country, TEXTSPLIT allows for this but defaults to a case sensitive match.
To change the case sensitivity of a delimiter the match_mode can be changed.
The options are 0 for a case sensitive match, this is the default so if you want case insensitive then this can be left blank, or 1 for a case insensitive match.
Using pad_with to fill in empty values
If the output of a TEXTSPLIT is uneven the function will fill in the empty values with a ‘#N/A’ error.
This mostly happens when you split a function by row and column using two separate delimiters.
Entering a value in pad_with will replace the ‘#N/A’ with whatever value you enter.
Conclusion
The TEXTSPLIT function can be used to dynamically separate out text in a cell based on a delimiter.
As a dynamic array function it will extend or contract depending on any changes made to the cell it is referencing with text in it.
The function allows for text to be split in columns or rows, as well as across columns and rows.
There are additional optional criteria to either ignore empty values, use case insensitive matches for text delimiters and pad text when the output is not even.
Comments