Counting Captial Letters!

[jgthtrotih]

hi,

this topic is semi-related to to the link below..its not need to look at it..its just for reference.

basically in this topic i just need help condensing the formula in cell "C18"

it just counts the capital letters in cell "B18"

http://www.mrexcel.com/forum/search.php?searchid=1894488

Excel Workbook
	A	B	C
18	Micheal Johnson Jones	M. J. J.	3
19	United States of America	U. S. A.	3
20	Laugh Out Loud	L. O. L.	3
Sheet7

Thanks!

 

[bosco_yip]

Counting no of captial letters without helper column

A18 : =Micheal Johnson Jones

C18, formula :

=SUMPRODUCT(LEN(A18)-LEN(SUBSTITUTE(A18,CHAR(ROW($65:$90)),)))

or,

=INDEX(FREQUENCY(CODE(MID(A18,ROW($1:$255),1)&"~"),{64,91}),2)

Regards
Bosco

 

[jgthtrotih]

bosco yip,

Thanks much for your formulas!

Thanks!

Happy Holidays!

 

[xenou]

Neat formulas! I missed the point of Armando's formula - yes, works on longer strings too.

 

[T. Valko]

Counting no of captial letters without helper column

A18 : =Micheal Johnson Jones

C18, formula :

=SUMPRODUCT(LEN(A18)-LEN(SUBSTITUTE(A18,CHAR(ROW($65:$90)),)))

or,

=INDEX(FREQUENCY(CODE(MID(A18,ROW($1:$255),1)&"~"),{64,91}),2)

Those will work but are susceptible to row insertions. Making the ROW ref absolute doesn't help.

The only way to prevent row insertions from affecting the formulas is to either use INDIRECT or use the INDEX method like in Domenic's formula.

 

[T. Valko]

#NAME?,

Thank You soo much in taking the time to write out that extremely in depth (for me at least) explanation on how Domenic's forumla works!..i was wondering where those numbers (77.5) were coming form and how you got them now i know!...you guys never fail me!

Thanks!

You're welcome!

I like explaining things. When you go step by step and see what's happening those complex formulas aren't so complex after all!

 

[jgthtrotih]

To All That Help With This Matter!,

Thank you all for your inputs and or forumlas! i really appreciate all the help.

This Site Is The Best!

Happy Holidays!

Thanks!