How to count the char "w"?

[Guest]

Does anyone have any suggestions on how to count the char "w" within cell A1?
For example, in cell A1, waterfall - wc, please ignore the upper / lower
case, I would like to count how many "w" or "W" inside the cell A1.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

 

[Bernard Liengme]

Try =LEN(A1)-LEN(SUBSTITUTE(A1,"w",""))
best wishes

 

[Sandy Mann]

Try:

=LEN(F7)-LEN(SUBSTITUTE(LOWER(F7),"w",""))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Try =LEN(A1)-LEN(SUBSTITUTE(A1,"w",""))

Bernard,

Doesn't fine upper case W

--
Regards,


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Rick Rothstein \(MVP - VB\)]

Try this...

=LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"w",""))

Rick

 

[David McRitchie]

Hi Eric,
Take the length of the original and subtract the length with the "W"
removed.,
add same for the lowercase, since SUBSTITUTE is case-sensitive.

=(LEN(A1)-LEN(SUBSTITUTE(A1,"W",""))) +
(LEN(A1)-LEN(SUBSTITUTE(A1,"w","")))

or

=LEN(A1)-LEN(SUBSTITUE(UPPER(A1),"W"))


More of the same type of things on my strings page
http://www.mvps.org/dmcritchie/excel/strings.htm

 

[Guest]

Thanks everyone for suggestions
Eric

Try =LEN(A1)-LEN(SUBSTITUTE(A1,"w",""))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email