Count the number of a range of words in a cell

  • Thread starter Thread starter Prashanth KR
  • Start date Start date
P

Prashanth KR

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.
 
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
 
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.
 
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
 
Thanks again Pete..... I was actually getting the error. But tried my own
logic by putting in "1" in the blank cells. But as U suggested it makes more
sense to update "XXXX" since their are probabilities of '1' appearing in the
searching cell.

Thanks again,
Prashanth KR.
 
Thanks for feeding back again.

XXXX will be okay as long as you do not have text about Australian
Lager!! Maybe better to make it XXXXXXXXXXXX.

Pete
 
Hey Pete,

I have got an other issue now. Iam actually looking for the term "OS" which
is Operating System. But its also counting whereever OS appears, say my cell
has a term "MICROSOFT" where OS is imbibed. I want the exact term in the
range to be counted.

Please help me out. I tried putting in the "Exact" formulae, but count not
succeed.

Prashanth KR.
 
If you are sure that OS will always appear with a space either side, then
you can enter it as <space>OS<space> in your list of words. However, you
might have a sentence with OS at the end, followed by a full-stop, or it
might be written as " ... OS-Windows XP ... ", so you have to decide whether
<space>OS or OS<space> would be more appropriate.

Hope this helps.

Pete
 
Thanks Pete... thanks for the clue. I shall use my logic to take appropriate
wordings in the list.

Thanks again,
Prashanth KR.
 
Back
Top