Abstract
Transcript
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, text and col_delimiter, are required.
Col_delimiter is short for "column delimiter", which will split text into separate columns.
In this case, the text comes from column B, and the delimiter is the @ symbol, which I need to wrap in double quotes to indicate a text value.
When I enter the formula, TEXTSPLIT divides the email at the @ symbol and returns the name and domain into separate cells.
Now I can copy the formula down to split all emails.
Let's look at another example
On this worksheet, we have some comma-separated text, also called "CSV text".
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.
The text comes from column B, and the delimiter is a comma, which I need to wrap in double-quotes.
When I enter the formula, TEXTSPLIT divides the text at each comma, and spills all five values into separate cells.
I can now copy the formula down to do the same for all rows.
Let's look at one more example.
In 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.
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.
To fix this problem, I need to change the delimiter to 3 characters: a space, an "x", and a space.
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.
