Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel: count uppercase letters in a cell
excel: how to count uppercase letters in a cell
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel: count uppercase letters in a cell
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel: count uppercase letters in a cell
Another one:
=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(INDIRECT("65:90"))),"") )) -- Biff Microsoft Excel MVP "harry bachrach" <harry wrote in message ... excel: how to count uppercase letters in a cell |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel: count uppercase letters in a cell
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 "T. Valko" wrote: Another one: =SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(INDIRECT("65:90"))),"") )) -- Biff Microsoft Excel MVP "harry bachrach" <harry wrote in message ... excel: how to count uppercase letters in a cell |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel: count uppercase letters in a cell
(I thought I'd responded to Harry's post....oh, well....it's late)
*********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: 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 "T. Valko" wrote: Another one: =SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(INDIRECT("65:90"))),"") )) -- Biff Microsoft Excel MVP "harry bachrach" <harry wrote in message ... excel: how to count uppercase letters in a cell |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel: count uppercase letters in a cell
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(A 1))),1))65)*(CODE(MID(A1,ROW(INDIRECT("A1:A"&LEN( A1))),1))<91)) Rick |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel: count uppercase letters in a cell
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(A 1))),1))65)*(CODE(MID(A1,ROW(INDIRECT("A1:A"&LEN( A1))),1))<91)) The 65 in the above formula should have been 64. =SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("A1:A"&LEN(A 1))),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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel: count uppercase letters in a cell
However, we can make this a lot shorter...
=SUMPRODUCT(1*(ABS(77.5-CODE(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)))<13) ) 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel: count uppercase letters in a cell
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel: count uppercase letters in a cell
"Leo Heuser" wrote...
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) 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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel: count uppercase letters in a cell
"Harlan Grove" wrote...
"Leo Heuser" wrote... .... =SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A 1))),1))= CODE(UPPER((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1 )))))+0) .... 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) |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel: count uppercase letters in a cell
The brute force approach I showed is at least easily adapted to
include any letters one would care to check. 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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel: count uppercase letters in a cell
"Harlan Grove" skrev i en meddelelse
... "Leo Heuser" wrote... 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(A 1))),1))= CODE(UPPER((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1 )))))+0) 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? 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. Sure, and my formula will work everywhere without any additional editing whatsoever. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel: count uppercase letters in a cell
"Leo Heuser" wrote...
.... 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? 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. Foolish interpretation then. But only the OP could confirm that. Sure, and my formula will work everywhere without any additional editing whatsoever. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do change entire worksheet to uppercase letters and lock it | Excel Worksheet Functions | |||
COUNT THE NUMBER OF LETTERS INCLUDING SPACES IN A CELL? | Excel Worksheet Functions | |||
How do i count numbers and letters to find a total count of all | Excel Worksheet Functions | |||
Why do all my cells automatically turn into uppercase letters? | Excel Discussion (Misc queries) | |||
CHANGE WHOLE EXCEL worksheet TO UPPERCASE LETTERS? | Excel Discussion (Misc queries) |