Abstract
Transcript
In this video, we'll take a look at the TEXTBEFORE function.
Like the name suggests, TEXTBEFORE is designed to extract text that occurs before a specific marker, called a "delimiter". A delimiter 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 name portion of each email address into column D.
As I start to enter TEXTBEFORE, Excel will match the function and I can use Tab to autocomplete. Although TEXTBEFORE accepts six arguments, 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 that I need to wrap the @ symbol in double quotes because it's a text value.
When I enter the formula, I get just the name. Now I can copy the formula down to extract the name from all emails.
Let's look at a new example.
On this worksheet, column B contains locations with three parts: City, State, and Zip code, each separated by a single space. The goal is to extract just the City and State.
In this case, a single space is the delimiter. However, we have more than one space.
How can we tell TEXTBEFORE which space to use?
By default, TEXTBEFORE counts from the left and will use the first delimiter it finds.
To use the second space, I can provide 2 for the third argument, which represents the instance of the delimiter.
You can see that this mostly works, except where the city is two words.
What we really want is a way to target the last space.
I can do that by providing a negative 1 for the instance number. This reverses the counting direction, and tells TEXTBEFORE to extract text before the last space.
With more complex data, you will often find that a negative instance number is a better, more robust option, because it can handle more variation in the data.
Next, let's look at how TEXTBEFORE handles case.
In this worksheet, I have dimensions in column B and I want the first number, width, in column D. Notice the delimiter here is actually 3 characters: a space, an x, and a space.
When I enter the formula, it mostly works, except for rows 8, 9, and 10, where I get an error.
If we look closer, we see that the "x" in these 3 rows is actually uppercase. TEXTBEFORE is case-sensitive by default, so it doesn't match the uppercase "X".
To ignore case, I need to provide 1 for the 4th argument, called match_mode. This tells TEXTBEFORE to perform a case-insensitive match.
Once I do that, the formula works as expected.
Note that TEXTBEFORE will always return a text value, even if the result is a number, as in this case. One easy way to get a numeric result is to add zero. The math operation causes Excel to perform the conversion automatically.
