Counting cells

[Frank Kabel]

...

"First walk, then talk."
"That was one of the walk's nicer features."

I bet I could break the nested function call limit.

lol thats not fair ;-)
Frank

 

[Frank Kabel]

What defines a separate word? No letters either before or after? If
so, define the name WordChars referring to

="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"

then if the strings to be tested were in A1:A10, try

=SUMPRODUCT(
ISERROR(FIND(MID(" "&A1:A10,SEARCH("?walk"," "&A1:A10),1),WordChars))
*ISERROR(FIND(MID(A1:A10&" ",SEARCH("walk?",A1:A10&"
")+4,1),WordChars)) )

Hi
to take blank rows into account I would amend this excellent solution
by
=SUMPRODUCT(ISERROR(FIND(MID(" "&A1:A10,SEARCH("?walk","
"&A1:A10),1),WordChars))

*ISERROR(FIND(MID(A1:A10&" ",SEARCH("walk?",A1:A10&"

")+4,1),WordChars))*(A1:A10<>""))

Frank

 

[Harlan Grove]

...
...

maybe I made a mistake by translating this into the German version but
this formula seems to count 10 - all instances there 'walk' is within
other words. So the range
walk
walking
walker

returns 7 for me?

My test data in A1:A10.

walk to me
walker, talker
to walk a long mile
when walking in Summer
crosswalk or middle of the block
crosswalks or dividing lines
can I walk
where's the crosswalk
how many crosswalks
talk-walk-talk

My formula (entered in cell F12, but that should be immaterial) is

=SUMPRODUCT(
ISERROR(FIND(MID(" "&A1:A10,SEARCH("?walk"," "&A1:A10),1),WordChars))
*ISERROR(FIND(MID(A1:A10&" ",SEARCH("walk?",A1:A10&" ")+4,1),WordChars))
)

My result is 4. With your 3 words, walk, walking and walker each entered in its
own cell in A18:A20, the formula

=SUMPRODUCT(
ISERROR(FIND(MID(" "&A18:A20,SEARCH("?walk"," "&A18:A20),1),WordChars))
*ISERROR(FIND(MID(A18:A20&" ",SEARCH("walk?",A18:A20&" ")+4,1),WordChars))
)

returns 1 on my machine (U.S. English version XL97 SR-2 under NT4 SP-6). What's
your *EXACT* formula?

 

[Frank Kabel]

My test data in A1:A10.

walk to me
walker, talker
to walk a long mile
when walking in Summer
crosswalk or middle of the block
crosswalks or dividing lines
can I walk
where's the crosswalk
how many crosswalks
talk-walk-talk

My formula (entered in cell F12, but that should be immaterial) is

=SUMPRODUCT(
ISERROR(FIND(MID(" "&A1:A10,SEARCH("?walk"," "&A1:A10),1),WordChars))
*ISERROR(FIND(MID(A1:A10&" ",SEARCH("walk?",A1:A10&"
")+4,1),WordChars)) )

My result is 4. With your 3 words, walk, walking and walker each
entered in its own cell in A18:A20, the formula

=SUMPRODUCT(
ISERROR(FIND(MID(" "&A18:A20,SEARCH("?walk","
"&A18:A20),1),WordChars)) *ISERROR(FIND(MID(A18:A20&"
",SEARCH("walk?",A18:A20&" ")+4,1),WordChars)) )

returns 1 on my machine (U.S. English version XL97 SR-2 under NT4
SP-6). What's your *EXACT* formula?

Hi Harlan
error on my side due to my tesdata (some rows were blanks). So your
formula works excellent unless there are blanks in between. They are
counted as matches

Frank

 

[Harlan Grove]

....
error on my side due to my tesdata (some rows were blanks). So your
formula works excellent unless there are blanks in between. They are
counted as matches

Not just blanks. It'll fail on any cell not containing the substring walk.

=SUMPRODUCT(
ISNUMBER(SEARCH("walk",A1:A10))
*ISERROR(FIND(MID(" "&A1:A10,SEARCH("?walk"," "&A1:A10),1),WordChars))
*ISERROR(FIND(MID(A1:A10&" ",SEARCH("walk?",A1:A10&" ")+4,1),WordChars))
)