That's the Rick I know and love! <g

Oh, thanks for posting that. I've been meaning to do some tests on something
and that formula reminded of it.


--
Biff
Microsoft Excel MVP


"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