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