Count the number of a range of words in a cell
You're welcome - glad to be of help.
I forgot to point out that you can always put "xxx" or "zzz" or some-such in
the unused cells of the C1:C10 range, to avoid errors from the formula
caused by blank cells. Then you can have a much larger range defined in the
formula and not have to amend it very often.
Pete
"Prashanth KR" wrote in message
...
Hi Pete,
Thank you very much. I appreciate your prompt reply and taking care of my
problem.
It really serves me a great deal. Thanks much again.
Prashanth KR.
"Pete_UK" wrote:
This seems to do it:
=SUMPRODUCT(((LEN(A1)-(LEN(SUBSTITUTE(UPPER(A1),UPPER(C1:C3),""))))/
LEN(C1:C3)))
I only tested it with three words, but make the range C1:C10 (twice)
if you have 10 words. Will return an error if any of the cells in the
range are empty.
Hope this helps.
Pete
On Feb 29, 9:22 am, Prashanth KR
wrote:
Hi,
I have a specific problem. Your timely help will be greatly
appreciated.
I have a text in a form of paragraph in A1 (eg., The BlackBerry e-mail
system is still unmatched. I set the service up with Gmail, Microsoft
Exchange Web Access, and Yahoo! Mail accounts within minutes. The
e-mail
system also supports attachments, displaying picture attachments, and
PowerPoints, but it boils PDFs and other Microsoft Office documents
down to
text).
I have a range of words say in C1:C10 (having Blackberry,
Microsoft..... etc)
I want to count the total no. of occurances of the words cited in
C1:C10.
Currently Iam able to count only one such instance by using the below
mentioned formulae:
=((LEN(A1)-(LEN(SUBSTITUTE(A1,C1,""))))/LEN(C1)) where C1 contains the
term
"Blackberry". Iam not able to substitute C1 with the range C1:C10.
Also it does not count if the sentence has a term which is
case-sensitive.
Kindly help.
Prashanth KR.
|