how efficient do you think this formula would be?

Not very.

You could limit the iterations by calculating the actual length of the
string but then there's go your nice short formula!


--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
Just out of curiosity, how efficient do you think this formula would be?
I'm not sure what drag the 999-iteration array calculations are putting on
the system (I wonder if there is a short cut mechanism built into the MID
function evaluator to not attempt to calculate past the end of the text),
although I'm guessing the SUBSTITUTE function involves an internal loop of
some kind to help offset it. And, of course, if we knew the text would
always be less than 100 characters in length, we could reduce the drag
from the array calculations by a factor of 10.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
If we assume the text is not longer than 999 characters (the limit can be
made larger if needed), this array-entered** formula really is "much"
shorter...

=SUM(--(MID(A4,ROW($1:$999),1)="s"))

<vbg

Rick

** For those reading this thread who are unfamiliar with array-entered
formulas, they are committed by pressing Ctrl+Shift+Enter instead of just
Enter.


"T. Valko" wrote in message
...
Yeah, but look how much shorter the UPPER version is!

I thought for sure you'd be "all over it". <BG

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote
in message ...
See inline comments...

A1 = St. Louis

=LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},""))

Fails

I'm not sure why you posted the above comment... no one posted that
formula.

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"S","s"},"")))

The above formula which you posted is the formula that Teethless mama
posted in the message you responded to.


Rick