Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count occurences of word in spreadsheet formula
I need to count the number of times specific words appear in a sheet , I'm
not having much luck on my own as the word might be within the cell but not constitute the whole contents. i.e looking for the word DOG in cell containing "the dog sat down" |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count occurences of word in spreadsheet formula
Try this:
=COUNTIF(A1:A100,"*dog*") -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "JBW" wrote in message ... I need to count the number of times specific words appear in a sheet , I'm not having much luck on my own as the word might be within the cell but not constitute the whole contents. i.e looking for the word DOG in cell containing "the dog sat down" |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count occurences of word in spreadsheet formula
=COUNTIF(rng,"*dog*")
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JBW" wrote in message ... I need to count the number of times specific words appear in a sheet , I'm not having much luck on my own as the word might be within the cell but not constitute the whole contents. i.e looking for the word DOG in cell containing "the dog sat down" |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count occurences of word in spreadsheet formula
=COUNTIF(C:C,"*dog*")
for column C, for example -- Gary''s Student - gsnu200752 "JBW" wrote: I need to count the number of times specific words appear in a sheet , I'm not having much luck on my own as the word might be within the cell but not constitute the whole contents. i.e looking for the word DOG in cell containing "the dog sat down" |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count occurences of word in spreadsheet formula
If you want the count of cells that contain dog (e.g., "a dog-eat-dog
world" counts as 1, not 2): =COUNTIF(A1:A100,"*dog*") If "dog-eat-dog" should count twice: =SUMPRODUCT(LEN(A1:A100) - LEN(SUBSTITUTE(A1:A100, "dog", ""))) / LEN("dog") In article , JBW wrote: I need to count the number of times specific words appear in a sheet , I'm not having much luck on my own as the word might be within the cell but not constitute the whole contents. i.e looking for the word DOG in cell containing "the dog sat down" |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count occurences of word in spreadsheet formula
I need to count the number of times specific words appear in a sheet , I'm
not having much luck on my own as the word might be within the cell but not constitute the whole contents. i.e looking for the word DOG in cell containing "the dog sat down" Just be aware of the "embedded word" problem that exists with the formulas that have posted. For example, each of them would count the "d-o-g" letter combination inside the word "boondoggle" as a hit for the word "dog". Rick |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count occurences of word in spreadsheet formula
"Rick Rothstein \(MVP - VB\)" wrote...
.... i.e looking for the word DOG in cell containing "the dog sat down" For the OP: that should be e.g. rather than i.e. Just be aware of the "embedded word" problem that exists with the formulas that have posted. For example, each of them would count the "d-o-g" letter combination inside the word "boondoggle" as a hit for the word "dog". The converse question begs for an answer: should the substring dogs in "It's gone to the dogs." count as an instance of dog? Anyway, if a count of all separate words 'dog' is sought, and word separators would include only space, comma, period and apostrophe, then one could use =SUMPRODUCT(LEN(" "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( x, "."," "),","," "),"'"," ")," "," ")&" ")-LEN(SUBSTITUTE(" "& SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(x,".", " "),","," "), "'"," ")," "," ")&" "," dog ","dog "))) However, if text parsing is a frequent task, Excel's built-in tools are fairly inadequate. Better to install Longre's MOREFUNC.XLL add-in and use its regular expression functions, e.g., =SUMPRODUCT(REGEX.COUNT(x,"\bdog\b")) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count occurences of word in spreadsheet formula
If that is the case then try this:
=SUM(COUNTIF(A1:A7,{"dog *","* dog *","* dog"})) "Rick Rothstein (MVP - VB)" wrote: I need to count the number of times specific words appear in a sheet , I'm not having much luck on my own as the word might be within the cell but not constitute the whole contents. i.e looking for the word DOG in cell containing "the dog sat down" Just be aware of the "embedded word" problem that exists with the formulas that have posted. For example, each of them would count the "d-o-g" letter combination inside the word "boondoggle" as a hit for the word "dog". Rick |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count occurences of word in spreadsheet formula
That would miss something like...
"Rover (my dog) is my best friend." The problem in trying to isolate words, as whole words, is being able to enumerate all of the possible delimiters for them. Rick "Teethless mama" wrote in message ... If that is the case then try this: =SUM(COUNTIF(A1:A7,{"dog *","* dog *","* dog"})) "Rick Rothstein (MVP - VB)" wrote: I need to count the number of times specific words appear in a sheet , I'm not having much luck on my own as the word might be within the cell but not constitute the whole contents. i.e looking for the word DOG in cell containing "the dog sat down" Just be aware of the "embedded word" problem that exists with the formulas that have posted. For example, each of them would count the "d-o-g" letter combination inside the word "boondoggle" as a hit for the word "dog". Rick |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count occurences of word in spreadsheet formula
"Rick Rothstein \(MVP - VB\)" wrote...
That would miss something like... "Rover (my dog) is my best friend." The problem in trying to isolate words, as whole words, is being able to enumerate all of the possible delimiters for them. .... Which is why Excel formulas, unadorned VB[A] and similar languages that provide only brute force, single character at a time text parsing features are very poor tools for text processing. Regular expressions, OTOH, are made for this. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to count occurences of word in spreadsheet formula
"Rick Rothstein \(MVP - VB\)" wrote...
That would miss something like... "Rover (my dog) is my best friend." The problem in trying to isolate words, as whole words, is being able to enumerate all of the possible delimiters for them. Which is why Excel formulas, unadorned VB[A] and similar languages that provide only brute force, single character at a time text parsing features are very poor tools for text processing. Regular expressions, OTOH, are made for this. Agreed. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Occurences | Excel Discussion (Misc queries) | |||
Count # of Occurences | Excel Discussion (Misc queries) | |||
count unique phrases in a word table or spreadsheet | Excel Worksheet Functions | |||
How can you count if the same word has been used in a Spreadsheet? | Excel Discussion (Misc queries) | |||
I would like to count # of occurences but have it be dynamic when. | Excel Worksheet Functions |