Excel IF Contains
Excel IF cell contains text
Excel IF contains
excel like wildcard
excel wildcard
excel like wildcard if
How can you test which cells in Excel contain text? Can you use an IF statement in Excel to find which cells contain certain text?
This video looks at COUNTIF, FIND, SEARCH, LOWER, SIGN, ISNUMBER, IFERROR functions.
Table of Contents
(0:00) Question
(0:22) Check for equality
(0:41) Wildcards fail in IF
(1:05) Using COUNTIF with wildcard
(1:39) Using FIND in Excel
(2:23) Adding SIGN, IFERROR, LOWER
(2:59) Using SEARCH in Excel
(3:25) Using ISNUMBER with SEARCH
(4:22) Excel IF contains solution
(4:44) Finding Contains using Filter
(5:11) Nancy Faust
Excel IF cell contains text
Excel IF contains
excel like wildcard
excel wildcard
excel like wildcard if
How can you test which cells in Excel contain text? Can you use an IF statement in Excel to find which cells contain certain text?
This video looks at COUNTIF, FIND, SEARCH, LOWER, SIGN, ISNUMBER, IFERROR functions.
Table of Contents
(0:00) Question
(0:22) Check for equality
(0:41) Wildcards fail in IF
(1:05) Using COUNTIF with wildcard
(1:39) Using FIND in Excel
(2:23) Adding SIGN, IFERROR, LOWER
(2:59) Using SEARCH in Excel
(3:25) Using ISNUMBER with SEARCH
(4:22) Excel IF contains solution
(4:44) Finding Contains using Filter
(5:11) Nancy Faust
Transcript of the video:
What an interesting question today.
We want to use Excel IF to see if a cell contains certain text. The answer I ended up with here today is not the answer I thought I would, and I'm still not sure that I have the best answer.
All right, so the question is we want to look through all of these phrases here, hundreds of phrases, and find the one that has this cell using an IF statement.
And if we're just checking for equality, is the cell to the left of me, B7, equal to C3.
I press F4 to put the $ signs in, double-click to copy that down, and that works great for lowercase "chart" and uppercase "Chart" and everything looks great. But we're not testing to see if it's equal.
We are testing to see if the word "chart" is anywhere in there.
And a lot of people, especially people familiar with SUMIF, COUNTIF and all of those, would think that you would do something like this, is B7=, and then here we build *&chart*.
That should work.
It works in SUMIF and COUNTIF, but when we copy that down, they don't see that as a match.
Which is a little frustrating because if we were using COUNTIF, that would certainly work.
Go look through this range of one cell and see how many contain *chart*.
And when we double-click and copy that down, it correctly finds that cell and that cell and those two cells and a bunch of other cells.
But using COUNTIF - so COUNTIF is designed to look through a whole big range of cells, and here we're using all of that logic to look through one cell.
That's not efficient and that's never the way that I did it when I was working in accounting.
My standard go-to for this was to use the FIND function.
So we're going to look for the word "chart" in cell B7.
The weird thing about FIND is FIND either returns a #Value!
error, which says it's not found, or a number that says where it's found.
So right here, that 13 means that the word "chart" is at the 13th position, which is great if we wanted to use LEFT or MID or something like that, but I just want to know is it there or not there?
And so then to modify FIND, I find that I'm doing all kinds of weird things, like I'm taking the SIGN of FIND, which will get me the numbers like 13 will turn back into a 1.
And then =IFERROR, 0, it's all just very clunky to be honest.
And look at this, it doesn't work with caps - that's the problem.
FIND is case-sensitive, so I'd have to come in here to this B7 and use the LOWER of B7.
And while I've done this hundreds of times, it's not the most efficient way.
The most efficient way is instead of using FIND is to use SEARCH.
The SEARCH function works just like FIND except for it's case-insensitive, so we're looking for "chart" inside of this cell. It'll tell us where it's found.
I'll double-click and copy that down.
You see, I get a hit there, a hit there, a hit there and hit there.
It's still a little weird that we're getting 13s and 1s and values, and what are we checking for?
But the awesome solution, I found this today at the MrExcel Message Board.
I'm like, "Well, let's go out and see what other people are using instead of FIND,".
And that's where I found SEARCH, and then this, send the result of this SEARCH into the ISNUMBER function.
So all of these values, the places where it's not found, are going to change to FALSE.
And all of the 1s or 13s or anything else are going to change to TRUE, because it is a number.
Let's turn the filters on here, Data, Filter, and we'll see that right now we are going to have two values, FALSEs and TRUEs.
The TRUEs are the ones that have "chart" somewhere in the cell.
Awesome.
Okay, that was four minutes of your life just to prove to you that this is really the way that Contains is probably best in Excel.
If we need to do an IF statement to see if it's equal to "chart", =@B7=C3 with $ signs in it, this says "Chart" otherwise "No chart".
That's easy, but if we need to see IF it contains, then it's this weird combination of ISNUMBER and SEARCH.
We're using SEARCH because it's able to find "chart" somewhere in there, whether it's uppercase or lowercase.
And then we're sending it into ISNUMBER to just get all of those different integers down to a TRUE and all of the value errors to a FALSE, and that works beautifully.
By the way, if you didn't need a formula...
I mean the question here in Google was, Excel IF, which I assume they meant the IF function, if we are just looking for phrases that contain the word "chart" then we don't need an IF statement at all. Just hit the filter here to turn that on.
Just open the drop-down, come here to search, search for "chart" and you're going to get all of the cells that contain "chart". Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
We want to use Excel IF to see if a cell contains certain text. The answer I ended up with here today is not the answer I thought I would, and I'm still not sure that I have the best answer.
All right, so the question is we want to look through all of these phrases here, hundreds of phrases, and find the one that has this cell using an IF statement.
And if we're just checking for equality, is the cell to the left of me, B7, equal to C3.
I press F4 to put the $ signs in, double-click to copy that down, and that works great for lowercase "chart" and uppercase "Chart" and everything looks great. But we're not testing to see if it's equal.
We are testing to see if the word "chart" is anywhere in there.
And a lot of people, especially people familiar with SUMIF, COUNTIF and all of those, would think that you would do something like this, is B7=, and then here we build *&chart*.
That should work.
It works in SUMIF and COUNTIF, but when we copy that down, they don't see that as a match.
Which is a little frustrating because if we were using COUNTIF, that would certainly work.
Go look through this range of one cell and see how many contain *chart*.
And when we double-click and copy that down, it correctly finds that cell and that cell and those two cells and a bunch of other cells.
But using COUNTIF - so COUNTIF is designed to look through a whole big range of cells, and here we're using all of that logic to look through one cell.
That's not efficient and that's never the way that I did it when I was working in accounting.
My standard go-to for this was to use the FIND function.
So we're going to look for the word "chart" in cell B7.
The weird thing about FIND is FIND either returns a #Value!
error, which says it's not found, or a number that says where it's found.
So right here, that 13 means that the word "chart" is at the 13th position, which is great if we wanted to use LEFT or MID or something like that, but I just want to know is it there or not there?
And so then to modify FIND, I find that I'm doing all kinds of weird things, like I'm taking the SIGN of FIND, which will get me the numbers like 13 will turn back into a 1.
And then =IFERROR, 0, it's all just very clunky to be honest.
And look at this, it doesn't work with caps - that's the problem.
FIND is case-sensitive, so I'd have to come in here to this B7 and use the LOWER of B7.
And while I've done this hundreds of times, it's not the most efficient way.
The most efficient way is instead of using FIND is to use SEARCH.
The SEARCH function works just like FIND except for it's case-insensitive, so we're looking for "chart" inside of this cell. It'll tell us where it's found.
I'll double-click and copy that down.
You see, I get a hit there, a hit there, a hit there and hit there.
It's still a little weird that we're getting 13s and 1s and values, and what are we checking for?
But the awesome solution, I found this today at the MrExcel Message Board.
I'm like, "Well, let's go out and see what other people are using instead of FIND,".
And that's where I found SEARCH, and then this, send the result of this SEARCH into the ISNUMBER function.
So all of these values, the places where it's not found, are going to change to FALSE.
And all of the 1s or 13s or anything else are going to change to TRUE, because it is a number.
Let's turn the filters on here, Data, Filter, and we'll see that right now we are going to have two values, FALSEs and TRUEs.
The TRUEs are the ones that have "chart" somewhere in the cell.
Awesome.
Okay, that was four minutes of your life just to prove to you that this is really the way that Contains is probably best in Excel.
If we need to do an IF statement to see if it's equal to "chart", =@B7=C3 with $ signs in it, this says "Chart" otherwise "No chart".
That's easy, but if we need to see IF it contains, then it's this weird combination of ISNUMBER and SEARCH.
We're using SEARCH because it's able to find "chart" somewhere in there, whether it's uppercase or lowercase.
And then we're sending it into ISNUMBER to just get all of those different integers down to a TRUE and all of the value errors to a FALSE, and that works beautifully.
By the way, if you didn't need a formula...
I mean the question here in Google was, Excel IF, which I assume they meant the IF function, if we are just looking for phrases that contain the word "chart" then we don't need an IF statement at all. Just hit the filter here to turn that on.
Just open the drop-down, come here to search, search for "chart" and you're going to get all of the cells that contain "chart". Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
