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"

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"

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"

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"

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"

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

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"))

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

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

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.

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