Sometimes, you’ll want to configure TEXTSPLIT to use multiple delimiters.
On this worksheet, we have some comma-separated text.
If I configure TEXTSPLIT to split the text using a comma…
It works fine for most of the data, but notice it fails in rows 8 to 12.
This happens because the delimiter in these rows is actually a semi-colon (;) instead of a comma.
To fix this problem, I can configure TEXTSPLIT to use more than one delimiter.
To do that, I need to use what’s called an “array constant”.
Then I’ll add a comma and the semi-colon in quotes.
When I update the formula and copy it down, TEXTSPLIT correctly splits text using both commas and semi-colons.
I can fix this problem by adding a comma and a space as a third delimiter.
When I update the formula, we have clean data in all rows.
Let’s look at a more complicated example.
Looking at the data, we can see the three dimensions are separated by a comma and a space.
However, I don’t really want those labels in there.
How can I get rid of them?
First, I’ll convert the delimiter to an array constant.
Next, I’ll add the labels as additional delimiters.
Notice I need to include the colon and the space.
Now when I update the formula, the labels are gone.
However, we’re now getting some extra columns in the output.
This happens because when use the labels as delimiters, we end up with empty values.
In other words, consecutive delimiters with no value in between.
I can fix this problem by setting the ignore_empty argument to TRUE.
Now TEXTSPLIT ignores empty values, and all dimensions are in the right columns.
I can actually take this one step further if I like.
