TEXTSPLIT with multiple delimiters (video)

Abstract

In this video, we look at two examples of configuring TEXTSPLIT with multiple delimiters. In the first example, we use three different delimiters to clean up some messy data. In the second example, we use multiple delimiters in an interesting and clever way. The first delimiter splits the text. The next three delimiters are used to remove labels that we don't want in the final output. Removing unwanted text is one of TEXTSPLIT's hidden powers.

Transcript

In this video, we'll look at how to use TEXTSPLIT with multiple delimiters.

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". This is just a fancy name for a hardcoded list of values in curly brackets

{"A","B","C"}

{1,2,3}

I'll start by adding curly brackets around the comma. 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.

However, notice that we still have a problem in the last 3 rows, where we have extra space leaking into the values.

This is happening because the delimiter in these 3 rows is actually a comma with a space and not just a comma.

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.

On this worksheet, we have a list of dimensions in column B and the goal is to extract the length, width, and height into separate columns.

Looking at the data, we can see the three dimensions are separated by a comma and a space.

If I configure TEXTSPLIT to use a comma and a space to split the dimensions, it works as expected.

However, I don't really want those labels in there. How can I get rid of them?

Well, TEXTSPLIT actually removes delimiters when it splits text, and we can use this feature to clean things up.

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. Assuming I don't need the label for centimeters, I can remove it too in the same way, by adding it as a delimiter.

Finally, I can add zero to force Excel to convert the text to numbers, and I have a clean set of numeric dimensions.