Abstract
Transcript
In this video, we'll take a look at the TEXTAFTER function.
TEXTAFTER is designed to extract text that occurs after a specific "delimiter", which can be one or more characters.
Let's look at an example.
In this worksheet, we have a list of email addresses. The goal is to extract the domain portion of each email into column D.
As I start to enter TEXTAFTER, Excel will match the function and I can use Tab to autocomplete. TEXTAFTER accepts six arguments, but only the first two arguments, text and delimiter, are required.
In this case, the text comes from column B, and the delimiter is the @ symbol. Notice I need to wrap the "@" symbol in double quotes to indicate a text value.
When I enter the formula, I get just the domain. Now I can copy the formula down to extract the domain from all emails.
Let's look at a new example.
On this worksheet, column B contains location data in three parts: City, State, and Zip, each separated by a single space. The goal is to extract just the zip code.
For these locations, a single space is the delimiter. However, the text contains more than one space.
By default, TEXTAFTER will use the first space it finds.
How can we tell TEXTAFTER to use the second space?
The trick is to enter a value for the third argument, called instance_num, which specifies the count of the delimiter you want to use.
To get text after the second space, I need to provide the number 2.
For this data, this works in most cases, but it fails when the city contains two or more words.
What we really need here is a way to target the last space.
Luckily, TEXTAFTER supports negative instance numbers.
If I use a negative 1 for instance number, it reverses the counting direction, and tells TEXTAFTER to split text at the last space.
Now the formula returns the zip code in all cases.
For many problems, you'll find that a negative instance number works better because it's more flexible and can handle variations in the data.
For the last example, let's look at how TEXTAFTER handles upper and lower case.
In this worksheet, I have dimensions in column B and I want the second number, length, in column D.
Notice the delimiter here is actually 3 characters: a space, an x, and a space. Also notice that the "x" in rows 8, 9, and 10 is uppercase.
How will TEXTAFTER handle this? Let's find out.
With a lowercase x, we do get the second number for most rows, but the formula fails where the x is uppercase.
This tells us that TEXTAFTER is case-sensitive by default.
One way to handle this situation is to ignore case, and I can do that by setting the fourth argument, called match_mode, to 1. This tells TEXTAFTER to perform a case-insensitive match.
Once I do that, the formula works as expected.
Note that TEXTAFTER will always return a text value, even if the result is a number, as in this case. One simple way to force a numeric result is to add zero. The math operation causes Excel to perform this conversion automatically.
