Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count # of Capitalized Characters
Is there a way to count the number of capitalized characters in a text cell?
For example, a cell value of "TTest" would return a result of 2. Any help is greatly appreciated. ME |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count # of Capitalized Characters
This can be done with an array formula Matt. Say you had the word TTesT in
cell G41... The following formula, entered into cell H41, should return 3: =SUM(LEN(G41)-LEN(SUBSTITUTE(G41,CHAR(ROW(INDIRECT("65:90"))),"" ))) Remember, enter as array formula. "Matt" wrote in message ... Is there a way to count the number of capitalized characters in a text cell? For example, a cell value of "TTest" would return a result of 2. Any help is greatly appreciated. ME |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count # of Capitalized Characters
Or just use =sumproduct():
=SUMPRODUCT(LEN(a1)-LEN(SUBSTITUTE(a1,CHAR(ROW(INDIRECT("65:90"))),"") )) (I changed the cell address to A1 from G41, too.) figdatbong wrote: This can be done with an array formula Matt. Say you had the word TTesT in cell G41... The following formula, entered into cell H41, should return 3: =SUM(LEN(G41)-LEN(SUBSTITUTE(G41,CHAR(ROW(INDIRECT("65:90"))),"" ))) Remember, enter as array formula. "Matt" wrote in message ... Is there a way to count the number of capitalized characters in a text cell? For example, a cell value of "TTest" would return a result of 2. Any help is greatly appreciated. ME -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count # of Capitalized Characters
Hi Matt
One way =SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1) )),1))64)* (CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<97)) -- Regards Roger Govier "Matt" wrote in message ... Is there a way to count the number of capitalized characters in a text cell? For example, a cell value of "TTest" would return a result of 2. Any help is greatly appreciated. ME |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to extract capitalized group in text string | Excel Worksheet Functions | |||
count characters | Excel Discussion (Misc queries) | |||
CAPITALIZED ALL WORDS FROM WROKSHEET | Excel Discussion (Misc queries) | |||
Count Characters | Excel Worksheet Functions | |||
convert capitalized text to small text (with Capitalized names an | Excel Discussion (Misc queries) |