Abstract
Transcript
In this video, we’ll take a look at the TEXTBEFORE function.
A delimiter can be one or more characters.
Let’s look at an example.
In this worksheet, we have a list of email addresses.
Although TEXTBEFORE accepts six arguments, only the first two arguments,textanddelimiter, 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.
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.
you might see that this mostly works, except where the city is two words.
What we really want is a way to target thelastspace.
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 thelastspace.
Next, let’s look at how TEXTBEFORE handles case.
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, calledmatch_mode.
This tells TEXTBEFORE to perform a case-insensitive match.
Once I do that, the formula works as expected.
One easy way to get a numeric result is to add zero.
The math operation causes Excel to perform the conversion automatically.
