Count the number of a range of words in a cell

[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.

 

[Pete_UK]

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

 

[Prashanth KR]

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]

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]

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.

 

[Pete_UK]

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

 

[Prashanth KR]

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.

 

[Pete_UK]

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

 

[Prashanth KR]

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

Thanks again,
Prashanth KR.

 

[Pete_UK]

You're welcome - hope we have it sorted now.

Pete