Count occurances of text in a cell

  • Thread starter Thread starter JayL.
  • Start date Start date
J

JayL.

Good day,

I have a sheet with numerous text strings all in the same column. I need to
search each and return the number of occurances (total) of the words
"medicare" and "eomb" cointained in each string.
I've tried this function:

=COUNTIF(A1:A10,"*medicare*")+COUNTIF(A1:A10,"*eomb*")
but it only counts 1 occurance of each. I need to the total times the word
'medicare occurs plus the total times 'eomb' occurs.

Any suggestions?
 
Hi,

Your formula should work - I tried it on my version (Excel 2002 SP2) and it
was OK, it also didn't matter if the words in the sheet were upper or lower
case. I know it doesn't solve your problem, but at least you know it should
work!
I looked in options and other places to try and find anything that might
cause it to fail but no luck. Perhaps someone else can shed some light on
it.

Cheers
Nigel
 
Good day, Jay

I'm not sure, what you are after. Is it *words* ( can exist at 3 places:
at the start of the string, inside the string and at the end of the string)
At the start, it's the characters followed by a space, inside the string
it's a space followed by the characters followed by a space, and at the
end it's a space followed by the characters.
OR
Are you looking for the *characters* no matter where they pop up (inside
another word)?

You say *words*, but your formula indicates *characters*, so here is a
solution for either:

For *words*, assuming the word to count for is entered in F1

=SUMPRODUCT((LEN(" " &A1:A10&" ")-LEN(SUBSTITUTE(" "&A1:A10&" "," "&F1&"
","")))/(LEN(F1)+2))

for *characters* (again entered in F1)

=SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,F1,"")))/LEN(F1))

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
 
Back
Top