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

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

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

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