Counting UPPERCASE letters in a range

Miro

unread,

Apr 27, 2003, 8:47:42 AM4/27/03

to

Hi,

can anyone help me with a tip on how to count the number
of uppercase letters in a range.

and counting lowecase as well?

thx

Miro

Chip Pearson

unread,

Apr 27, 2003, 8:59:07 AM4/27/03

to

Miro,

If you mean the number of cells that contain all upper or lower
case, use

=SUMPRODUCT(EXACT(A1:A5,UPPER(A1:A5))*1)
and
=SUMPRODUCT(EXACT(A1:A5,LOWER(A1:A5))*1)

for upper and lower case, respectively.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Miro" <marwanm...@wanadoo.fr> wrote in message
news:035c01c30cbb$31c30d30$a601...@phx.gbl...

Dave Peterson

unread,

Apr 27, 2003, 9:02:33 AM4/27/03

to

Is a worksheet formula ok?

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(INDIRECT("65:90"))),"")))
hit ctrl-shift-enter instead of just enter. This is an array formula and if you
do it correctly, excel will wrap curly brackets {} around the formula.

Or non-array entered:
=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(INDIRECT("65:90"))),"")))

--

Dave Peterson
ec3...@msn.com

Dave Peterson

unread,

Apr 27, 2003, 9:15:41 AM4/27/03

to

Change 65:90 to 97:122 for lower case.

I used a formula like: =code("A") to get the numbers.

--

Dave Peterson
ec3...@msn.com

Marwan MOUBARAK

unread,

Apr 27, 2003, 9:22:36 AM4/27/03

to

It works!!!
Thanks! you're a life saver!

Miro

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

David McRitchie

unread,

Apr 27, 2003, 5:34:00 PM4/27/03

to

Hi Marwan,
Two completely different solutions, based on interpretation.
Chip's - number of cells all in uppercase within a selection
Dave P's - number of characters in uppercase through all cells
in selection, and the answer you selected as your choice.

Despite some ambiguity the nice thing about newsgroups is
that Chip's answer might help someone else.

Your feedback is especially appreciated, because there were
multiple interpretations and/or multiple solutions.

David McRitchie,

"Marwan MOUBARAK" <marwanm...@wanadoo.fr> wrote ...

David McRitchie

unread,

Apr 27, 2003, 6:05:19 PM4/27/03

to

Hi Marwan,
Just realized Dave Peterson's reply was for a single cell
and you actually asked for a range.

Dave Peterson

unread,

Apr 27, 2003, 7:35:06 PM4/27/03

to

Oops. Why do you actually read the questions? <bg>.

this seems to work:

=SUM(LEN(A1:A10)
-LEN(SUBSTITUTE(A1:A10,TRANSPOSE(CHAR(ROW(INDIRECT("65:90")))),"")))
(all one cell, still array entered)

--

Dave Peterson
ec3...@msn.com

David McRitchie

unread,

Apr 27, 2003, 8:36:28 PM4/27/03

to

Hi Dave,
I have an undeserved reputation as a trouble maker <grin>.
The poster already liked your previous answer, just wanted to
make sure he got his money's worth. If I could have modified
it, I would have, but I couldn't. I've added both of your answers
to my strings.htm page. The amazing thing is this was all
Worksheet Functions.
---
David McRitchie

"Dave Peterson" <ec3...@msn.com> wrote ...