Counting # of occurances of words in text
Wow...this is a good education. I have since made only one more change and
that is to add "$" to the range. This will allow me to copy the formula
down, keeping the same range and change "good" to another word (b3) I would
be searaching for. . What I am doing is to count keywords on customer
responses . Hoping to get more "good's" than "bad's" :) Thanks again.
"Peo Sjoblom" wrote in message
...
It wasn't my formula, just added the sumproduct to it since the OP tried
to use it on a range, I was too lazy to add anything else. Btw, I use
either UPPER or LOWER which is better than multiple SUBSTITUTE
Nor would I use /4, I would use a cell reference like B2 then /LEN(B2)
B2 would obviously replace "Good" wherever it occurs
=SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(LOWER(A1:A10),LOWER(B2),"")))/LEN(B2))
that would take care of typos like gooD as well
--
Regards,
Peo Sjoblom
|