MrExcel's Learn Excel #497 - Finding Substrings

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Apr 21, 2009.
George sends in a question How can COUNTIF be made to look for cells that match a pattern? Episode 497 shows the long workaround to this problem.

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!
maxresdefault.jpg


Transcript of the video:
Hey, Well come back to MrExcel netcast.
I'm Bill Jelen.
Here's a question that popped up at the Excel seminar and also send in by George, who send it in for the podcast and they both came in the same week.
So, I figure this is a good one to cover.
We have a data set here, and we have some various values in Column B and we want to count how many times a certain value occurs.
And so, normally we would use the COUNTIF function. COUNTIF B6 to B27 comma and then the value test and it tells us that there's two cells that have the word test.
But unfortunately, in both cases both people we're looking for any cells that contain the value, but that wasn't the complete value.
So, if we look for ABC, you'll see that it says that there's only one cell, but if I cruise down through here, there is a value cell that has ABC, but there's also a cell that contains XYZ, ABC and COUNTIF isn't finding it if it's a substring.
All right so, that's a big long set up to our solution here, to solve this it's actually a three-step process.
The first thing I'm going to do is I'm going to come up and create a brand new cell come out of the way that has the value that I'm looking for that's step number one, and then I'm going to add a brand new column to my data set called there, and that there formula is basically using the fine function and saying fine ABC with in cell B6, and that's either going to return a value. for example, if ABC start at the fourth position, We give me the number for or it's going to return an error.
All right now, I don't want a value or an error I instead want to convert that to either be true or false.
So, I wrap that FIND function in an ISERROR function which gives me true when it's not there And false when it's not and then I use the NOT function to reverse that around.
So, that way that there is actually returning true when the values found.
So, now that I have that formula and I copy down to all of my cells and we'll go back and change the COUNTIF.
=COUNTIF, we'll look through the range of the new column that has either true or false and ask to see how many are true and and there it finds the ones that are substrings as well.
Really tricky solution to what sounds like it should be a simple problem.
Unfortunately, it's one of those things that requires three steps.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top