Excel TEXTSPLIT function

Abstract

In this video, we’ll take a look at the TEXTSPLIT function.

TEXTSPLIT is an Excel function designed to split text into separate cells using a given delimiter.

In this worksheet, we have a list of email addresses.

The goal is to split each email into a separate name and domain.

As I start to enter TEXTSPLIT, Excel will match the function and I can use Tab to autocomplete.

Although TEXTSPLIT accepts six arguments, only the first two arguments,textandcol_delimiter, are required.

Col_delimiter is short for “column delimiter”, which will split text into separate columns.

Now I can copy the formula down to split all emails.

Each row contains five values: first name, last name, age, city, and state.

And each value is separated by a comma.

To split this text into the five columns to the right, we can use the TEXTSPLIT function.

I can now copy the formula down to do the same for all rows.

Let’s look at one more example.

The dimensions are separated by an “x”.

Sometimes, you’ll want to use more than one character as the delimiter.

To illustrate, I’ll first split this text using only an “x”.

This works, but notice that we are picking up extra space in the width and height.

This happens because we didn’t include any space in our delimiter.

TEXTSPLIT divides the text at each “x”, but it leaves the space intact.

When I update the formula, we get the dimensions without extra space.

This is a good reminder that TEXTSPLIT actually removes the delimiter when it splits text.

This is a useful feature that we’ll look at in more detail in an upcoming video.