If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Counting words within a merged range of cells
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. |
#2
|
|||
|
|||
Counting words within a merged range of cells
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 |
#3
|
|||
|
|||
Counting words within a merged range of cells
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". "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. |
#4
|
|||
|
|||
Counting words within a merged range of cells
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 |
Thread Tools | |
Display Modes | |
|
|