Hello and welcome to this video tutorial
from Computergaga.com. And in this video we are going to look at how to use
the IF function of Excel, to test for a partial match on text. So I have some
addresses in a column here. And I want to determine whether they are local, or not.
And the scenario is that they are local if the postcode begins with CB2. That is
our area. Anything outside of that we are considering not local. Now we can’t use
an IF function in the typical way and just test if is equal to CB2, because
there’s a lot of other text in those cells. So we want to know if the cell
contains CB2. A partial match on it. It is not a complete string. So in cell B2
here, column B is where it’s happening. I will begin an IF function. I’ll just zoom
in so we can see exactly what’s going on here. And with this IF function, as
mentioned already, I can’t just select the cell next to it
and test if it’s equal to CB2. I will be using this string for CB2, but it’s not
an exact match on that. So what I’m going to do is bring in a function called
SEARCH. Now the SEARCH function as it details on screen here, will return the
number of a character at which a specific character or string (so for us
CB2), is found. So it’s telling us it’s going to return a number if that character of
string is found, reading it left to right. And then says it’s not case-sensitive.
Now there is a function in Excel called FIND as well. So we could I either use FIND or use SEARCH. And in this example we could do either. You could argue that
FIND is actually better because of the example I’m doing. But the key difference
between them is that FIND is case-sensitive. So maybe that one’s
better because I am taking case into account here with CB2. But it’s more
common to see people use SEARCH in this example, so that you’re not taking the
case of the letters into account. So I’m going to open up this SEARCH function so
it prompts me for it’s trying to find. What I’m trying to find. And that is CB2.
Already entered from before. Comma. Where are we looking for it. In cell A2. And
then the starting number I’m not going to worry about. So that’s whereabouts in cell A2 do we begin to search for CB2. And I could set
it to start from the first character, or the 6th character. But in this example
it’s all kind of redundant. There’s only a one unique mention of that, and I don’t
know whereabouts it’s going to be. So I’m just going to close off the bracket.
And that is the SEARCH function that will search for that text. And if it
finds it, returns the number of its position. And if it doesn’t, no number.
So outside of that function. I’m going to bring in another function called ISNUMBER. And this one a guess kind of speaks for itself. Its going to check if
there’s a number. And it will return true if there is, and false if it’s not. So we’re now talking the language of IF. If wants to run a value if it’s true, and a
value if it’s false. And we now have a function that will behave in that way, and
display the words true or false, if there’s a number. Exactly what we need. So closing bracket on the end there. If that SEARCH function comes back with a number, it means it’s found it. ISNUMBER will say true. We’re now on the IF function. I’ll
put my comma in to close off that logical test. What do we want if it’s
true. What do we want if it’s false. If it’s true I’m going to show the word
“Local”. And if it’s false maybe I’ll put the word “Far”. Close off the bracket. Let’s
see this formula in action. I’ll copy that down to the bottom. And
here we have it. Those with CB2 are determined as local. And those without are determined as far. Now you notice down here. Look at this.
We have a problem. We’ve got one here where it’s CB25. And it’s been set up as
local, because it does have CB2 in it. Now that is wrong. That’s not CB2, that’s CB25. So let’s go back up to my first formula. And just make a little edit to
what we’re searching for. And I’m going to put a space on the end of it. So only
if you find CB2 followed by a space. Then it’s local. I’ll press Enter. Let’s
try that again. I’ll copy it down. Ah now CB25 is far. That is a more accurate
example for us right now. So that’s the kind of thing that you’ll
need to take into account when doing partial matches as well. Just like when
you use Find and Replace in Excel, or in Word. You know. Really powerful features
but you got to get that right. It can also be misleading if you don’t do that correct
find of in that string. Now I wanted to take this one step further again. Because
some of you might be wondering. What if we consider CB2 and CB3 to be local.
What if both of those are. Okay. Let’s go back to our formula then. Let me just do
a different zoom this time so it’s not too crazy. Our formulas about to get get larger. So i’ll shrink that. Because we need to bring in
the OR function at the start here. So OR. Logical one, logical two. I’m just going to
copy what I’ve done. So ISNUMBER(SEARCH blah blah blah A2.
Gonna take a copy of that. Stick a comma after where I am at the moment. Paste it
in again, but change the CB2 to CB3 space. So, and then just one more
bracket after this one here. Now this is what we want. Let me just come out a
little bit. Trying to fit this on my screen. So OR function. We’ve got an ISNUMBER CB2 comma ISNUMBER CB3 so if either of them are the case then it’s
local. Then it’s far. Maybe we’ll make the decision to get rid of that far as well.
So it’s a little bit clearer. I’ll just put an empty string in there.
So if we run this one. And if I just widen this column again so we can see
what’s going on. And here we have it. CB3 or CB2 are local. Anything else
including the CB25, not local. Something else. So that is using the IF function
with some partial matches. A cell that contains scenario. I hope you found that
video useful. Please check out some of our other video tutorials on our YouTube
channel. And come check us out at Computergaga.com