Thanks to many viewers who wrote in after Episode 497, there is a much easier way to count the number of cells that contain a certain substring. In Episode 505, learn how to use the wildcard options in COUNTIF and AutoFilter.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. Well I'm back from the data analyst boot camp that was held last week in Frisco Texas. Want to thank everyone who came up the boot camp. We had a great time with 40 people.
I have a lots of new questions, and also some great tips to share with you over the next week or so Also last Thursday podcast number 500. Want to thank everyone who entered that contest.
By the time this podcast airs. I will have chosen the winners.
You can go to podcast500.html@ MrExcel.com to see the winners.
I'll be sending emails out and allow people to choose their prizes from among the 40 great prizes.
We had donated from Q and wholly macro books. The other thing that we had is last Monday I think it was episode 497, someone wrote in with a question about how they could search for a substring and I went through this big long convoluted solution And I love the podcast audience because when I say something that's wrong, Boy, I hear about it. I got phone calls I got emails. Everyone saying hey Bill, there's a much better way to do that.
So I appreciate everyone who wrote in.
Basically what I had done was I said that if we wanted to look through column B and find any cells that have ABC, I basically wrote this formula that says look through that column and see if you find the value ABC.
Now I mentioned that that will return a number if it's found.
For example, if it's found in the fourth position return the number 4 or an error and so we had to use the =ERROR function and then the NOT function.
And it was really a big long convoluted hassle.
It turns out that the COUNTIF function, well it allow you to use wild cards.
So we don't need to add the extra column.
we don't need to do any of that just say we want to COUNTIF all the cells from B6:B27 and then we're looking for "*ABC*" and sure enough it goes through and finds anything that contains ABC so even it has AABCD, it will find it as a result.
The other cool feature here that someone pointed out is that this also works with the Autofilter.
If we would turn on Data Filter AutoFilter We get the Autofilter drop-down rather than being able to use one of the built in choices, we would go to custom and say we want to look for something that contains ABC Click OK and we will get just the ABC records from the group.
So there you go.
Using either the contains or the wild card option and COUNTIF, a much better way to look for a substring within a column of text.
Hey, thanks for stopping by. Will see you next time for another netcast from MrExcel.
I'm Bill Jelen. Well I'm back from the data analyst boot camp that was held last week in Frisco Texas. Want to thank everyone who came up the boot camp. We had a great time with 40 people.
I have a lots of new questions, and also some great tips to share with you over the next week or so Also last Thursday podcast number 500. Want to thank everyone who entered that contest.
By the time this podcast airs. I will have chosen the winners.
You can go to podcast500.html@ MrExcel.com to see the winners.
I'll be sending emails out and allow people to choose their prizes from among the 40 great prizes.
We had donated from Q and wholly macro books. The other thing that we had is last Monday I think it was episode 497, someone wrote in with a question about how they could search for a substring and I went through this big long convoluted solution And I love the podcast audience because when I say something that's wrong, Boy, I hear about it. I got phone calls I got emails. Everyone saying hey Bill, there's a much better way to do that.
So I appreciate everyone who wrote in.
Basically what I had done was I said that if we wanted to look through column B and find any cells that have ABC, I basically wrote this formula that says look through that column and see if you find the value ABC.
Now I mentioned that that will return a number if it's found.
For example, if it's found in the fourth position return the number 4 or an error and so we had to use the =ERROR function and then the NOT function.
And it was really a big long convoluted hassle.
It turns out that the COUNTIF function, well it allow you to use wild cards.
So we don't need to add the extra column.
we don't need to do any of that just say we want to COUNTIF all the cells from B6:B27 and then we're looking for "*ABC*" and sure enough it goes through and finds anything that contains ABC so even it has AABCD, it will find it as a result.
The other cool feature here that someone pointed out is that this also works with the Autofilter.
If we would turn on Data Filter AutoFilter We get the Autofilter drop-down rather than being able to use one of the built in choices, we would go to custom and say we want to look for something that contains ABC Click OK and we will get just the ABC records from the group.
So there you go.
Using either the contains or the wild card option and COUNTIF, a much better way to look for a substring within a column of text.
Hey, thanks for stopping by. Will see you next time for another netcast from MrExcel.
