excel: count uppercase letters in a cell

  • Thread starter Thread starter Guest
  • Start date Start date
harry bachrach said:
excel: how to count uppercase letters in a cell
Click to expand...

One way,

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{"A";"B";"C";"D";"E";"F";
"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";
"W";"X";"Y";"Z"},"")))
 
If we know that no text will be longer than 256 characters...

Then maybe this?:

=INDEX(FREQUENCY(CODE(MID(A1,COLUMN($1:$65536),1)&"~"),{64,91}),2)


***********
Regards,
Ron

XL2002, WinXP
 
(I thought I'd responded to Harry's post....oh, well....it's late)

***********
Regards,
Ron

XL2002, WinXP
 
excel: how to count uppercase letters in a cell

Not the shortest formula of the bunch... just another method to accomplish
the task.

=SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))>65)*(CODE(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))<91))

Rick
 
excel: how to count uppercase letters in a cell
Not the shortest formula of the bunch... just another method to accomplish
the task.

=SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))>65)*(CODE(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))<91))
Click to expand...

The >65 in the above formula should have been >64.

=SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))>64)*(CODE(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))<91))

However, we can make this a lot shorter...

=SUMPRODUCT(1*(ABS(77.5-CODE(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)))<13))

Still not the shortest though (within 9 characters of it).

Rick
 
However, we can make this a lot shorter...
=SUMPRODUCT(1*(ABS(77.5-CODE(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)))<13))
Click to expand...

We can shave 2 more characters off of the above...

=SUMPRODUCT(1*(ABS(77.5-CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))<13))

Rick
 
harry bachrach said:
excel: how to count uppercase letters in a cell
Click to expand...

Contrary to the other suggestions, here's one that works for
all characters not only the characters of the English alphabet <bg>

=SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=
CODE(UPPER((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))))+0)
 
Leo Heuser said:
Contrary to the other suggestions, here's one that works for
all characters not only the characters of the English alphabet <bg>

=SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=
CODE(UPPER((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))))+0)
Click to expand...

You might want to consider testing before you post. And maybe reading OPs
CAREFULLY. Where, Oh Great Internationalizer, did the OP mention that the
cells s/he would be checking would include ONLY letters? Or do you have such
feable grasp of how UPPER works that you're ignorant of the fact that it
returns the same character for NON-LETTERS?

If cell A1 contained

Leo Heuser makes FOOLISH, CONDESCENDING responses.

the correct number of upper case letters is 22, but your Oh So Wonderful AND
Inclusive! formula returns 29. Why? because it also includes the spaces,
comma and period in the count.

Correct results are even more important to handling other languages in the
sense that you might as well make it correct FOR AT LEAST ONE LANGUAGE. At
least all the other responses managed a correct count for English (and,
FWIW, Hawaiian and perhaps all Polynesian languages).

The brute force approach I showed is at least easily adapted to include any
letters one would care to check. Why, even you should be able to figure out
how to adapt it without screwing up.
 
....

FWIW, you were close to getting it right. Let me help you.

=SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>
CODE(LOWER((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))))+0)
 
The brute force approach I showed is at least easily adapted to
include any letters one would care to check.
Click to expand...

Here is another method to count the upper case letters that should also be
easily adaptable to include any set of characters one would want to count...

=SUMPRODUCT(1*(NOT(ISERR(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ",1)))))

Rick
 
Harlan Grove said:
You might want to consider testing before you post. And maybe reading OPs
CAREFULLY. Where, Oh Great Internationalizer, did the OP mention that the
cells s/he would be checking would include ONLY letters? Or do you have
such feable grasp of how UPPER works that you're ignorant of the fact that
it returns the same character for NON-LETTERS?
Click to expand...

Quote
"how to count uppercase letters in a cell"
Unquote

And where did s/he mention, that the cells would include ANYTHING ELSE but
letters?
That's YOUR interpretation. MINE is, that the OP is talking about strings of
letters. That's what I tested for, and my formula works under that
condition.
The brute force approach I showed is at least easily adapted to include
any letters one would care to check. Why, even you should be able to
figure out how to adapt it without screwing up.
Click to expand...

Sure, and my formula will work everywhere without any additional editing
whatsoever.
 
Leo Heuser said:
Quote
"how to count uppercase letters in a cell"
Unquote

And where did s/he mention, that the cells would include ANYTHING ELSE but
letters?
Click to expand...

OK, classic semantic games. OP didn't actually mention whether there'd ever
be ANYTHING in these cells, just asked how to count particular things if
they were there. We could then assume there'd ever be only upper case
letters, in which case =LEN(A1) would have been the obvious answer. Why
didn't you post that?
That's YOUR interpretation. MINE is, that the OP is talking about strings
of
letters. That's what I tested for, and my formula works under that
condition.
Click to expand...

Foolish interpretation then. But only the OP could confirm that.
Sure, and my formula will work everywhere without any additional editing
whatsoever.
Click to expand...

As long as your extremely naive 'interpretation' holds. But if the even more
obvious interpretation that the OP only cares about English letters, your
post was irrelevant. OTOH, since you obviously meant your post as a
generalization, yours was an extremely narrow generalization - handles
languages using accented Latin characters as long as strings contain only
letters. Not particularly robust as generalizations go.
 
Back
Top