This is why I asked you to post some samples!

As you can see, the formula has gotten to be somewhat complicated. This is
because the formula has to be able to distinguish "nn" as a unique word so
that you don't get "false positives" associated with other words that might
also contain the string "nn", for example: beginning.

If "nn" is already a unique word (no other words will also contain the
string "nn") then you can probably use a less complicated formula.

That's why I asked you to post some samples!

Without a very explicit explanation or samples we can only take out best
guess.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
This is why I asked you to post some samples!

--
Biff
Microsoft Excel MVP


"Ken Curtis" wrote in message
...
Yes, I will. Thanks. But ... I do not see how your formula will be able
to
count how many times the string 'nn' (or whatever) appears in a cell.
T.Valco had a great (and short) solution. It is this: =SUMPRODUCT(LEN("
"&D4:D15&" ")-LEN(SUBSTITUTE(" "&(D4:D15)&" "," nn ","")))/4 . But it
has a
problem. If there is one nn in the cell it returns 1as the count, but if
there are two of them (i.e. nn nn), it still counts 1. Oddly, if I
seperate
the string (nn ai nn) it then counts two nn's - which is the correct
result.
Do you have any thoughts on this? Oh, it also counts 2 consecutive nn's
if I
put two spaces between them - which, obviously, I do not want to do.
--
Ken Curtis


"Rick Rothstein" wrote:

Give this variation of Biff's formula a try...

=SUMPRODUCT(LEN(SUBSTITUTE(" "&A1:A10&" "," "&C1&" "," "&C1&" "))-
LEN(SUBSTITUTE(UPPER((SUBSTITUTE(" "&A1:A10&" "," "&C1&" "," "&
C1&" "))),UPPER(" "&C1&" "),"")))/(LEN(C1)+2)

--
Rick (MVP - Excel)


"Ken Curtis" wrote in message
...
Yes, this works ... sort of. Here's the odd problem: 'nn' returns 1
,
'nn
ai nn' returns 2, however 'nn nn' returns only 1 (not 2, as it
should).
Suggestions?
--
Ken Curtis


"T. Valko" wrote:

"nn" is a 'word'

OK, that means "nn" should have spaces on either side of it if it's
within
the string.

nn is the nn
it's nn time!
no time for nn

Based on those examples this formula returns 4:

All on one line

=SUMPRODUCT(LEN(" "&A1:A3&" ")-LEN
(SUBSTITUTE(" "&UPPER(A1:A3)
&" "," NN ","")))/4

If you want to use a cell to hold the criteria:

C1 = nn

=SUMPRODUCT(LEN(" "&A1:A3&" ")-LEN
(SUBSTITUTE(" "&UPPER(A1:A3)
&" ",UPPER(" "&C1&" "),"")))/(LEN(C1)+2)


--
Biff
Microsoft Excel MVP


"Ken Curtis" wrote in message
...
T.Valko

"nn" is a 'word'
--
Ken Curtis


"T. Valko" wrote:

It depends on how "nn" is to be treated. Is "nn" to be considered
a
"word"
by itself or can it be part of any word?

For the best possible solution it would be a good idea if you
posted
some
samples of your data and the expected result.

--
Biff
Microsoft Excel MVP


"Ken Curtis" wrote in
message
...
Hi,
I am using a function (=COUNTIF(data,"*"&nn&"*") that searches a
range
of
cells and tells me how many times "nn" appears. It's fine.
However,
if
"nn"
appears twice in a single cell it is only counted once - not
twice.
How
do I
have "nn" (or anything else) counted for the number of times it
appears
in
a
range including it appearing twice (or more times) in a single
cell?
--
Ken Curtis