Count # of Capitalized Characters

M

Matt

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
 
F

figdatbong

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

Dave Peterson

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.)
 
R

Roger Govier

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))
 

Ask a Question

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.

Ask a Question

Similar Threads

How can this Micro customized. Please help 4
Counting characters 3
Capitalizing text from the left limited by a comma 4
check first 3 characters of a cell and compare to a table 4
Transforming many cells of all caps into first letter capitals onl 5
Count Characters in A Cell 3
Excel Need Countifs Formula Help 0
Count Characters in a cell 8

Top