Counting words within a merged range of cells

[Pman]

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.

 

[Ron Rosenfeld]

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

 

[JLatham]

Try this formula:
=(LEN(D3)-LEN(SUBSTITUTE(UPPER(A15),UPPER(B9),"")))/LEN(B9)

It should accept A15 as the address for the merged range. The way it works
is it compares the UPPERCASE equivalents of both what is in B9 and the merged
cell and replaces exact matches with 'nothing' (actually an empty string).
It then finds the number of characters removed by subtracting new length from
old length and dividing that result by the length of whatever is in B9. The
result is the number of times that the phrase from B9 appeared in the merged
cell.

Use caution - a phrase in B9 like 'the' could result in erroneous results.
Consider this being in the merged cell: "The theologians threw their hats
into that ring over there." With "the" in B9, the formula above would tell
you that "the" appeared 4 times, when it really only appears once. So when
looking for simple one-word entries, try to remember to do something to make
it more unique and less likely to be found as part of another word, such as
entering "the " into B9 instead of just "the".

 

[Ron Rosenfeld]

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