Faizee from Youtube posts a question about looking for certain words in a cell in order to create a category. While this sounds simple, Bill and Mike manage to come up with a number of possible formulae in Episode 1055!
This is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of 377 tips from the book!
This is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of 377 tips from the book!
Transcript of the video:
Hey, yeah Bill Jelen from MrExcel.Com.
I've got a cool Excel tip for you today.
Hey, this is my Mike Girvin from Excel Is Fun on YouTube.
And I have a different way to do that.
Hey, All right welcome back to another dueling Excel podcast.
Great question today.
That's going to generate a lot of different answers.
I think it's send from YouTube from Faizee.
We have some items here and says if cell C7 contains either the word paper/sticker, then the category becomes paper/stick.
Other wise the category becomes card.
All right so, as I start to attack this, I think about the FIND command.
I want to FIND word paper within that cell.
And FIND is going to give me a number.
And I really don't care about the number.
OR it is going to give me an error.
So I'm interested in wrapping that whole function in the ISERROR function.
Copy that down.
And that's going to basically tell me if, paper is not found.
If I get a false yeah the paper is there.
If I get a true paper is not there.
So it's saying that there's an error.
Now as I start to think about this one, this is a tricky problem.
Because we have to check for paper, We have to check for card and so, I'd like to go back to my Electrical Engineering days.
And build something called Karnaugh map.
Now with a Karnaugh map you take a look at all the possible conditions.
So, I check to see if paper is not there and I can get either a true or a false and if sticker is not there, I get either a true or false.
And build a little table here Of what the answer should be.
And usually when you see this table, visually you'll understand the one condition that you're checking for or the few conditions that you're checking for.
And in this case, I'm looking for where both of the ISERROR FIND formula fragments are generating truths, where both of those are true.
That's the place where I want a right to put the word card.
So, that leads me to this Formula here.
Rather long formula, I'll Admit.
I check to see if the ISERROR find a paper, and he is a refined of sticker, I put both of those together into an AND condition.
If both of those are true then my Karnaugh map tells me that I'm supossed to book card.
otherwise paper/stick.
So, I enter that and Copy it down and sure enough.
It works if the word paper is there or if the word stickers there, We get paper/stick otherwise we get card now.
You know I've thought about this, and I said well, I wonder if we could shorten that Formula at all.
Maybe using a CSE Formula, CTRL+SHIFT+ENTER formula.
And I said wouldn't it be cool if, the FIND Function could handle an array of values.
So what's happening here is, I'm asking the FIND Function to look for both paper and stick within C7.
Take the ISERROR of that and then take the and that whole big long thing.
If both of those are true, if both of those items are returning a 1 or a true then we put the word card other wise paper/stick.
Now to make this work, CTRL+SHIFT+ENTER.
It's because we have an array in there and copy it down a little bit of special copying.
And It works with an array formula.
This certainly would be the way to go if you had three, four, five, six different categories to check.
Because you can just put them right here, inside the curly brackets.
And could check them all.
If all of them were true, then you have a certain value.
Otherwise, you have another vibe.
All right well, that's my take on it.
Let's send this over to Mike and see what Mike has to do.
Thanks MrExcel.
Hey, now!
That was a great formula.
Funny thing is I came up with my formula.
And it's like exactly the opposite.
Except for it's still almost the same.
Now watch this.
He started off with IF which is what I did and then he had an AND I had an OR.
And then he had an ISERROR.
I had an ISNUMBER and then he used FIND.
FIND was the key for finding whether paper or card or sticker was in the cell.
FIND his case sensitive.
I has decided to use SEARCH, which is exactly the same as Find except for, its not case sensitive.
And the FIND text we're going to use that Array syntax.
So,curly bracket and then in quotes paper and double quote.
Now a comma means a column.
I'm going to use a semicolon which means row.
That is how in array syntax you can make a full table, using array Syntax.
And then the same thing sticker and double quote and and curly bracket.
Now comma, and the with intact same thing, Close parentheses, close parenthesis close parenthesis.
Now, we're back to the the let this whole thing right here, is the logical test.
Then we have to say what the value_if_true and value_if_false.
Now let's just look at this, the SEARCH and the FIND.
If there's no difference in Case, then they'll return the same thing .
So MrExcel's formula, and mine will turn the same thing.
But watch this ISNUMBER now paper and sticker.
We're looking for that.
When I ask for is number, let's just take a look here.
If I, highlight this and hit the F9 key to evaluate it.
Notice, it says five and value.
Now, I'm asking ISNUMBER.
So, I can find either one of these.
Which is why I use the OR.
As soon as it sees once, it knows that it's either paper OR sticker.
That's why ISNUMBER works here.
It says IS or OR, one of these a number, this will be true this will be false.
In the OR we'll see that one of them are true.
The way Mr.Excel did it is, both of these had to come out to be value errors.
Which means SEARCH didn't find it.
That's why he used ISERROR.
So, when they're both errors then the AND which means both have to be true, will evaluate to true.
Now, I'm going to CTRL+Z what that all means is that I've determined to find paper or sticker.
So the value IF true, because of the way, I set up the OR ISNUMBER and SEARCH.
That comes as the value of two.
whereas, MrExcel's Formula card, came as the value of true.
So, then my value of fault is simply card.
And then end double quote, close parenthesis.
Now, also another interesting in aspect.
When you use this array syntax in some formulas, you actually don't have to use CTRL+SHIFT+Enter.
So, I'm just going to hit Enter and then copy it down.
To show you another example of sometimes not of having this array syntax.
And not requiring CTRL+SHIFT+Enter.
How about summing the largest to.
Right, so I said large.
And give me the first largest and second largest.
That's another example when you have this array syntax.
And you don't use CTRL+SHIFT+ENTER.
It just knows to work.
Now, a couple other possibilities here.
If I look over here instead of all that, we can do IF OR and replace this.
Replace right here.
This is a little construct that looks at everything up to the space and replaces it with a blank.
So, then that replace will find the paper, paper card, card sticker.
So that right there checks to see against paper and sticker.
So, again.
Either one of these come out to be true the OR will say true.
And then it dumps paper and sticker or card if False The another possibility is VLOOKUP that same replace right here.
It's just going to pull out the paper card sticker, and then VLOOKUP looks it up in the first column.
And dumps this in to the cell.
So, those are options also.
All right, we'll see you in next trick.
Yeah, Mike that was completely the same as mine, while being completely different.
And actually a lot better.
So, point to Mike.
Hey, want to thank you for stopping by.
We'll see you next week for another dueling podcast from MrExcel and Excel Is Fun.
I've got a cool Excel tip for you today.
Hey, this is my Mike Girvin from Excel Is Fun on YouTube.
And I have a different way to do that.
Hey, All right welcome back to another dueling Excel podcast.
Great question today.
That's going to generate a lot of different answers.
I think it's send from YouTube from Faizee.
We have some items here and says if cell C7 contains either the word paper/sticker, then the category becomes paper/stick.
Other wise the category becomes card.
All right so, as I start to attack this, I think about the FIND command.
I want to FIND word paper within that cell.
And FIND is going to give me a number.
And I really don't care about the number.
OR it is going to give me an error.
So I'm interested in wrapping that whole function in the ISERROR function.
Copy that down.
And that's going to basically tell me if, paper is not found.
If I get a false yeah the paper is there.
If I get a true paper is not there.
So it's saying that there's an error.
Now as I start to think about this one, this is a tricky problem.
Because we have to check for paper, We have to check for card and so, I'd like to go back to my Electrical Engineering days.
And build something called Karnaugh map.
Now with a Karnaugh map you take a look at all the possible conditions.
So, I check to see if paper is not there and I can get either a true or a false and if sticker is not there, I get either a true or false.
And build a little table here Of what the answer should be.
And usually when you see this table, visually you'll understand the one condition that you're checking for or the few conditions that you're checking for.
And in this case, I'm looking for where both of the ISERROR FIND formula fragments are generating truths, where both of those are true.
That's the place where I want a right to put the word card.
So, that leads me to this Formula here.
Rather long formula, I'll Admit.
I check to see if the ISERROR find a paper, and he is a refined of sticker, I put both of those together into an AND condition.
If both of those are true then my Karnaugh map tells me that I'm supossed to book card.
otherwise paper/stick.
So, I enter that and Copy it down and sure enough.
It works if the word paper is there or if the word stickers there, We get paper/stick otherwise we get card now.
You know I've thought about this, and I said well, I wonder if we could shorten that Formula at all.
Maybe using a CSE Formula, CTRL+SHIFT+ENTER formula.
And I said wouldn't it be cool if, the FIND Function could handle an array of values.
So what's happening here is, I'm asking the FIND Function to look for both paper and stick within C7.
Take the ISERROR of that and then take the and that whole big long thing.
If both of those are true, if both of those items are returning a 1 or a true then we put the word card other wise paper/stick.
Now to make this work, CTRL+SHIFT+ENTER.
It's because we have an array in there and copy it down a little bit of special copying.
And It works with an array formula.
This certainly would be the way to go if you had three, four, five, six different categories to check.
Because you can just put them right here, inside the curly brackets.
And could check them all.
If all of them were true, then you have a certain value.
Otherwise, you have another vibe.
All right well, that's my take on it.
Let's send this over to Mike and see what Mike has to do.
Thanks MrExcel.
Hey, now!
That was a great formula.
Funny thing is I came up with my formula.
And it's like exactly the opposite.
Except for it's still almost the same.
Now watch this.
He started off with IF which is what I did and then he had an AND I had an OR.
And then he had an ISERROR.
I had an ISNUMBER and then he used FIND.
FIND was the key for finding whether paper or card or sticker was in the cell.
FIND his case sensitive.
I has decided to use SEARCH, which is exactly the same as Find except for, its not case sensitive.
And the FIND text we're going to use that Array syntax.
So,curly bracket and then in quotes paper and double quote.
Now a comma means a column.
I'm going to use a semicolon which means row.
That is how in array syntax you can make a full table, using array Syntax.
And then the same thing sticker and double quote and and curly bracket.
Now comma, and the with intact same thing, Close parentheses, close parenthesis close parenthesis.
Now, we're back to the the let this whole thing right here, is the logical test.
Then we have to say what the value_if_true and value_if_false.
Now let's just look at this, the SEARCH and the FIND.
If there's no difference in Case, then they'll return the same thing .
So MrExcel's formula, and mine will turn the same thing.
But watch this ISNUMBER now paper and sticker.
We're looking for that.
When I ask for is number, let's just take a look here.
If I, highlight this and hit the F9 key to evaluate it.
Notice, it says five and value.
Now, I'm asking ISNUMBER.
So, I can find either one of these.
Which is why I use the OR.
As soon as it sees once, it knows that it's either paper OR sticker.
That's why ISNUMBER works here.
It says IS or OR, one of these a number, this will be true this will be false.
In the OR we'll see that one of them are true.
The way Mr.Excel did it is, both of these had to come out to be value errors.
Which means SEARCH didn't find it.
That's why he used ISERROR.
So, when they're both errors then the AND which means both have to be true, will evaluate to true.
Now, I'm going to CTRL+Z what that all means is that I've determined to find paper or sticker.
So the value IF true, because of the way, I set up the OR ISNUMBER and SEARCH.
That comes as the value of two.
whereas, MrExcel's Formula card, came as the value of true.
So, then my value of fault is simply card.
And then end double quote, close parenthesis.
Now, also another interesting in aspect.
When you use this array syntax in some formulas, you actually don't have to use CTRL+SHIFT+Enter.
So, I'm just going to hit Enter and then copy it down.
To show you another example of sometimes not of having this array syntax.
And not requiring CTRL+SHIFT+Enter.
How about summing the largest to.
Right, so I said large.
And give me the first largest and second largest.
That's another example when you have this array syntax.
And you don't use CTRL+SHIFT+ENTER.
It just knows to work.
Now, a couple other possibilities here.
If I look over here instead of all that, we can do IF OR and replace this.
Replace right here.
This is a little construct that looks at everything up to the space and replaces it with a blank.
So, then that replace will find the paper, paper card, card sticker.
So that right there checks to see against paper and sticker.
So, again.
Either one of these come out to be true the OR will say true.
And then it dumps paper and sticker or card if False The another possibility is VLOOKUP that same replace right here.
It's just going to pull out the paper card sticker, and then VLOOKUP looks it up in the first column.
And dumps this in to the cell.
So, those are options also.
All right, we'll see you in next trick.
Yeah, Mike that was completely the same as mine, while being completely different.
And actually a lot better.
So, point to Mike.
Hey, want to thank you for stopping by.
We'll see you next week for another dueling podcast from MrExcel and Excel Is Fun.
