count number of time a text string appears

Did a search on the forum but didn't find a similar thread. I have a very large spreadsheet and want to count the number of times a particular text string shows up in a column. I can't use autofilter due to the 1000 limit.

Here's an example, Column C contains:
Dan Parker
John Doe
Dan Smith
Jill Smith

So if I search on *Dan*, the function should return a count of 2.

I've used COUNTIF before to return values when the whole cell = a certain value but in my case the cell may have 200 characters and I want to count based on a fuzzy search. I would like to do this in a function and not a macro. Any help would be appreciated.

Originally Posted by wolfpack95

Did a search on the forum but didn't find a similar thread. I have a very large spreadsheet and want to count the number of times a particular text string shows up in a column. I can't use autofilter due to the 1000 limit.

Here's an example, Column C contains:
Dan Parker
John Doe
Dan Smith
Jill Smith

So if I search on *Dan*, the function should return a count of 2.

I've used COUNTIF before to return values when the whole cell = a certain value but in my case the cell may have 200 characters and I want to count based on a fuzzy search. I would like to do this in a function and not a macro. Any help would be appreciated.

Hi

You can use COUNTIF:

=COUNTIF(A:A,"*Dan*")

This isn't a case-sensitive search, so this will pick up Dan, Daniel, Shodan etc.

There isn't a 1000 limit on Autofilter - what there is is a 1000 limit on the unique values that can be displayed in the Autofilter dropdown, but the filtered results on the sheet will contain all the matching values.

Hope this helps!

Richard

Originally Posted by RichardSchollar

Hi

You can use COUNTIF:

=COUNTIF(A:A,"*Dan*")

This isn't a case-sensitive search, so this will pick up Dan, Daniel, Shodan etc.

There isn't a 1000 limit on Autofilter - what there is is a 1000 limit on the unique values that can be displayed in the Autofilter dropdown, but the filtered results on the sheet will contain all the matching values.

Hope this helps!

Richard

Well, that was easy . I tried this before posting but had an error in my formula. It's working now. Thanks.

Hey guys, I saw this, and it helps me, but I have a slightly different problem. I am looking for the total number of times a name appears within a field, not just how many fields it appears in.

For example, if a field had

Dan Dan
Dan Parker
John Doe
Dan Smith
Jill Smith

then that code would produce a 3. I want the code that would produce the overall number of times that the string was found. Which is 4. Can somebody please help me with that?

Except if there's some word that contrains Dan (Dante, Prodan)

I'm not sure I'm allowed to do this on somebodies thread, but I read with interest and I have an issue probably a whole lot more simple yet similar (in my mind anyway!)

I have a table of numbers and am trying to ascertain pairs within each horizontal line i.e. how many times if a 1 appears does a 2 also appear. It's a repetitive task as I have to do the same for 1 & 3, then 1 & 4 up to 20 and then start the process again on 2 ie. 2&1 2&3 2&4 etc... takes me bloody ages!!

I figured about doing =if(1,A2:D5,(countif(A2:D5,2))"") I attached the example but it returns errors that I can't figure out. You guys inspired me to sign up for a VB course to take things a step further but I'm only one month in and still on forms etc so not sure if you can help me with either the VB or a formula?

regards

Simon

apologies have posted hopefully correctly and referred to the html. hope that's ok.