On Thu, 13 May 2010 20:35:11 -0400, Ron Rosenfeld
wrote:

On Thu, 13 May 2010 14:37:01 -0700, Pman
wrote:

Hi Everyone,

I have a merged range of A15:J469, and I'm trying to find the number of
times a specific words appears in this range, some which have spaces (e.g.:
sun life, which is also entered in cell B9 - i.e. whatever is entered in B9
is the search string). It needs to search for sun life regardless of the case
(since it'd be Sun life if it appears at the beginning of a sentence). Is
there a way to count the multiple number of times a specific word appears in
the merged range? I'd appreciate any help I can get.

Thanks.

In general, it's not such a great idea to use merged ranges. But if you do,
the contents will be in the upper left cell.

So one way to count the occurrences of the phrase in B9 would be:

=(LEN(A15) - LEN(SUBSTITUTE(A15,B9,"")))/LEN(B9)

This would, however count 2 in the case of

B9: far
A15:J469 far farthing

If that is a problem, then a more sophisticated method will be required.

--ron

I should have mentioned, as JLatham's suggestion mentioned and reminded me,
that the SUBSTITUTE worksheet function is case sensitive -- another possible
issue in counting the words.
--ron