G

#### Guest

excel: how to count uppercase letters in a cell

G

excel: how to count uppercase letters in a cell

Ad

H

One way,harry bachrach said:excel: how to count uppercase letters in a cell

=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"},"")))

T

Another one:

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

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

G

Then maybe this?:

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

***********

Regards,

Ron

XL2002, WinXP

G

***********

Regards,

Ron

XL2002, WinXP

R

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

Ad

R

The >65 in the above formula should have been >64.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))

=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

R

We can shave 2 more characters off of the above...=SUMPRODUCT(1*(ABS(77.5-CODE(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)))<13))

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

Rick

L

Contrary to the other suggestions, here's one that works forharry bachrach said:excel: how to count uppercase letters in a cell

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)

H

You might want to consider testing before you post. And maybe reading OPsLeo 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)

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.

H

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)

Ad

R

Here is another method to count the upper case letters that should also beinclude any letters one would care to check.

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

L

QuoteHarlan 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?

"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.

Sure, and my formula will work everywhere without any additional editingThe 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.

whatsoever.

H

OK, classic semantic games. OP didn't actually mention whether there'd everLeo 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?

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?

Foolish interpretation then. But only the OP could confirm 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.

As long as your extremely naive 'interpretation' holds. But if the even moreSure, and my formula will work everywhere without any additional editing

whatsoever.

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.

Ad

- Joined
- Oct 16, 2015

- Messages
- 1

- Reaction score
- 0

how to find a capital letter in a cell in excel for example [ mAnoj= A ?

**Want to reply to this thread or ask your own question?**

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.