Please help
Cell a:a5 contains words, B1:B5 respective amount
A1 "Carls" 50
A2 "carls, rene" 60
A3 "carls;" 70
A4 "carlsberg" 80
A5 "carls " 90
I want an excel formular on cell C6 to countif word "carls" is typed on cell A6, "1" typed in cell B6 will bring 50 as first occurance of "carls", if I change cell B6 to "4" the corresponding amount 90 is a result.
Please note that Carlsbeg should not be considered but the rest like "Carls", "carls," , "Carls;", "Carls" should mean "carls".
Bob Phillips wrote:
=COUNTIF(rng,"*dog*")-- HTHBob(there's no email, no snail mail, but somewhere
30-Oct-07
=COUNTIF(rng,"*dog*"
-
HT
Bo
(there is no email, no snail mail, but somewhere should be gmail in my addy)
Previous Posts In This Thread:
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"
Re: how to count occurences of word in spreadsheet formula
Try this
=COUNTIF(A1:A100,"*dog*"
--
HTH
R
====================================================
Please keep all correspondence within the Group, so all may benefit
====================================================
I need to count the number of times specific words appear in a sheet , I'
not having much luck on my own as the word might be within the cell but no
constitute the whole contents
i.e looking for the word DOG in cell containing "the dog sat down"
=COUNTIF(rng,"*dog*")-- HTHBob(there's no email, no snail mail, but somewhere
=COUNTIF(rng,"*dog*"
-
HT
Bo
(there is no email, no snail mail, but somewhere should be gmail in my addy)
RE: how to count occurences of word in spreadsheet formula
=COUNTIF(C:C,"*dog*"
for column C, for exampl
-
Gary''s Student - gsnu20075
:
If you want the count of cells that contain dog (e.g.
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"
Just be aware of the "embedded word" problem that exists with the formulas
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
Re: how to count occurences of word in spreadsheet formula
..
...
For the OP: that should be e.g. rather than i.e
The converse question begs for an answer: should the substring dogs i
"It's gone to the dogs." count as an instance of dog
Anyway, if a count of all separate words 'dog' is sought, and wor
separators would include only space, comma, period and apostrophe
then one could us
=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 tool
are fairly inadequate. Better to install Longre's MOREFUNC.XLL add-i
and use its regular expression functions, e.g.
=SUMPRODUCT(REGEX.COUNT(x,"\bdog\b"))
Re: how to count occurences of word in spreadsheet formula
If that is the case then try this:
=SUM(COUNTIF(A1:A7,{"dog *","* dog *","* dog"}))
:
That would miss something like... "Rover (my dog) is my best friend.
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
......
...
....
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
Agreed.
Rick
Submitted via EggHeadCafe - Software Developer Portal of Choice
Changing WCF Service Implementation at Runtime
http://www.eggheadcafe.com/tutorial...ng-wcf-service-implementation-at-runtime.aspx