| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of occurances of words in text
=(LEN(A1)-LEN(SUBSTITUTE(A1,"good","")))/4
If the word to serch is in a particular cell, say B1, then: =(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1) "Mike G" wrote in message ... If I have a cell that contains "good things come in good packages" and want to count the number of occurances of the word "good" how would I do that? Have tried countif and sumproduct and both give me #name? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting # of occurances of words in text
Thanks for the formulas.....If I wanted to check more that one cell i.e.
a1:a25 I thought I could replace the A1 with A1:A25, I get the #value again... Thanks for your patience! "PCLIVE" wrote in message ... =(LEN(A1)-LEN(SUBSTITUTE(A1,"good","")))/4 If the word to serch is in a particular cell, say B1, then: =(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1) "Mike G" wrote in message ... If I have a cell that contains "good things come in good packages" and want to count the number of occurances of the word "good" how would I do that? Have tried countif and sumproduct and both give me #name? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting # of occurances of words in text
You can but you need to use another function to sum it
=SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"good","")))/4) -- Regards, Peo Sjoblom "Mike G" wrote in message ... Thanks for the formulas.....If I wanted to check more that one cell i.e. a1:a25 I thought I could replace the A1 with A1:A25, I get the #value again... Thanks for your patience! "PCLIVE" wrote in message ... =(LEN(A1)-LEN(SUBSTITUTE(A1,"good","")))/4 If the word to serch is in a particular cell, say B1, then: =(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1) "Mike G" wrote in message ... If I have a cell that contains "good things come in good packages" and want to count the number of occurances of the word "good" how would I do that? Have tried countif and sumproduct and both give me #name? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting # of occurances of words in text
Thank you Peo....that works perfectly.
"Peo Sjoblom" wrote in message ... You can but you need to use another function to sum it =SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"good","")))/4) -- Regards, Peo Sjoblom "Mike G" wrote in message ... Thanks for the formulas.....If I wanted to check more that one cell i.e. a1:a25 I thought I could replace the A1 with A1:A25, I get the #value again... Thanks for your patience! "PCLIVE" wrote in message ... =(LEN(A1)-LEN(SUBSTITUTE(A1,"good","")))/4 If the word to serch is in a particular cell, say B1, then: =(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1) "Mike G" wrote in message ... If I have a cell that contains "good things come in good packages" and want to count the number of occurances of the word "good" how would I do that? Have tried countif and sumproduct and both give me #name? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting # of occurances of words in text
Your formula only work for "good", if OP has "GOOD", or "Good" then your
formula will not work. The formula below will take care all that. =SUMPRODUCT(--(LEN(A1:A100)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A100,"good ",""),"Good",""),"GOOD",""))))/4 "Peo Sjoblom" wrote: You can but you need to use another function to sum it =SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"good","")))/4) -- Regards, Peo Sjoblom "Mike G" wrote in message ... Thanks for the formulas.....If I wanted to check more that one cell i.e. a1:a25 I thought I could replace the A1 with A1:A25, I get the #value again... Thanks for your patience! "PCLIVE" wrote in message ... =(LEN(A1)-LEN(SUBSTITUTE(A1,"good","")))/4 If the word to serch is in a particular cell, say B1, then: =(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1) "Mike G" wrote in message ... If I have a cell that contains "good things come in good packages" and want to count the number of occurances of the word "good" how would I do that? Have tried countif and sumproduct and both give me #name? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting # of occurances of words in text
I always thought substitute was case insensitive - but I get the same
results. You could shorten it to: =SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(LOWER(A1:A10),"good","")))/4) "Teethless mama" wrote: Your formula only work for "good", if OP has "GOOD", or "Good" then your formula will not work. The formula below will take care all that. =SUMPRODUCT(--(LEN(A1:A100)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A100,"good ",""),"Good",""),"GOOD",""))))/4 "Peo Sjoblom" wrote: You can but you need to use another function to sum it =SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"good","")))/4) -- Regards, Peo Sjoblom "Mike G" wrote in message ... Thanks for the formulas.....If I wanted to check more that one cell i.e. a1:a25 I thought I could replace the A1 with A1:A25, I get the #value again... Thanks for your patience! "PCLIVE" wrote in message ... =(LEN(A1)-LEN(SUBSTITUTE(A1,"good","")))/4 If the word to serch is in a particular cell, say B1, then: =(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1) "Mike G" wrote in message ... If I have a cell that contains "good things come in good packages" and want to count the number of occurances of the word "good" how would I do that? Have tried countif and sumproduct and both give me #name? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting # of occurances of words in text
It wasn't my formula, just added the sumproduct to it since the OP tried to
use it on a range, I was too lazy to add anything else. Btw, I use either UPPER or LOWER which is better than multiple SUBSTITUTE Nor would I use /4, I would use a cell reference like B2 then /LEN(B2) B2 would obviously replace "Good" wherever it occurs =SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(LOWER(A1:A10),LOWER(B2),"")))/LEN(B2)) that would take care of typos like gooD as well -- Regards, Peo Sjoblom "Teethless mama" wrote in message ... Your formula only work for "good", if OP has "GOOD", or "Good" then your formula will not work. The formula below will take care all that. =SUMPRODUCT(--(LEN(A1:A100)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A100,"good ",""),"Good",""),"GOOD",""))))/4 "Peo Sjoblom" wrote: You can but you need to use another function to sum it =SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"good","")))/4) -- Regards, Peo Sjoblom "Mike G" wrote in message ... Thanks for the formulas.....If I wanted to check more that one cell i.e. a1:a25 I thought I could replace the A1 with A1:A25, I get the #value again... Thanks for your patience! "PCLIVE" wrote in message ... =(LEN(A1)-LEN(SUBSTITUTE(A1,"good","")))/4 If the word to serch is in a particular cell, say B1, then: =(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1) "Mike G" wrote in message ... If I have a cell that contains "good things come in good packages" and want to count the number of occurances of the word "good" how would I do that? Have tried countif and sumproduct and both give me #name? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting # of occurances of words in text
Wow...this is a good education. I have since made only one more change and
that is to add "$" to the range. This will allow me to copy the formula down, keeping the same range and change "good" to another word (b3) I would be searaching for. . What I am doing is to count keywords on customer responses . Hoping to get more "good's" than "bad's" :) Thanks again. "Peo Sjoblom" wrote in message ... It wasn't my formula, just added the sumproduct to it since the OP tried to use it on a range, I was too lazy to add anything else. Btw, I use either UPPER or LOWER which is better than multiple SUBSTITUTE Nor would I use /4, I would use a cell reference like B2 then /LEN(B2) B2 would obviously replace "Good" wherever it occurs =SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(LOWER(A1:A10),LOWER(B2),"")))/LEN(B2)) that would take care of typos like gooD as well -- Regards, Peo Sjoblom |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting 7 and 8 occurances | Excel Discussion (Misc queries) | |||
| Counting occurances | Excel Discussion (Misc queries) | |||
| Counting Occurances | Excel Discussion (Misc queries) | |||
| counting text example of a cell with multiple words inside | Excel Discussion (Misc queries) | |||
| counting text example of a cell with multiple words inside | Excel Discussion (Misc queries) | |||
Hybrid Mode
