Abstract
Transcript
In this video, we'll look at some examples where we need to use the TEXTAFTER function together with the TEXTBEFORE function.
When splitting text with a formula in Excel you'll often find that you need to use more than one function.
In this first example, we have a list of codes in column B and the goal is to extract just the color into column D.
Each code has 3 parts, and the delimiter is a hyphen (-).
If I use TEXTAFTER to get the text after the first hyphen, I get both the color and the number.
If I use TEXTBEFORE to get the text before the second hyphen, I get the 2-letter code at the start, plus the color.
So how can I get the color only?
The trick is to use the TEXTAFTER and TEXTBEFORE functions together.
First, I'll use the TEXTAFTER function to remove the 2-letter code.
Next, I'll add the TEXTBEFORE function.
Since TEXTAFTER has already removed the 2-letter code, only the color and number remain.
This means I can pass the result from TEXTAFTER directly into TEXTBEFORE, and use a hyphen as the delimiter.
The result is the color only.
Let's look at a new example.
In this worksheet, we have location data in column B. The goal is to extract just the state name into column D.
As before, I'll start with the TEXTAFTER function. To remove the city, I'll provide the locations for the text with a comma and a space for the delimiter.
This gives me the state name and the zip code.
To remove the zip code, I can use TEXTBEFORE. The text comes from TEXTAFTER. The delimiter is a single space.
This works, but notice I've got a problem in the last 4 rows, since South Dakota contains two words.
Because I didn't provide an instance number for the delimiter, TEXTBEFORE defaults to 1, which won't work for states that contain 2 names.
An easy fix is to provide a negative one for instance number. This causes TEXTBEFORE to count backwards and use the last space.
As you can see, this works correctly.
You might wonder if it matters if I start with TEXTBEFORE or TEXTAFTER?
In this problem, no.
If I start with TEXTBEFORE, I need to use a negative 1 for instance number to remove the zip code.
Then I can use TEXTAFTER like I did originally, with a comma and space for the delimiter.
The result is exactly the same.
