Counting Captial Letters!

jgthtrotih

Well-known Member
Joined
Aug 28, 2009
Messages
568
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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
ABC
18Micheal Johnson JonesM. J. J.3
19United States of AmericaU. S. A.3
20Laugh Out LoudL. O. L.3
Sheet7


Thanks!
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Try...

=SUMPRODUCT(--(ABS(CODE(MID(B18,ROW(INDEX(A:A,1):INDEX(A:A,LEN(B18))),1))-77.5)<=12.5))
 

jgthtrotih

Well-known Member
Joined
Aug 28, 2009
Messages
568
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Domenic,
this works..its confusing on how it works..but it does thanks!
Try...

=SUMPRODUCT(--(ABS(CODE(MID(B18,ROW(INDEX(A:A,1):INDEX(A:A,LEN(B18))),1))-77.5)<=12.5))

Armando Montes,

this works perfectly also...confusing lol

Try this one:
=LEN(SUBSTITUTE(B18," ",""))/2

This site is great!

Thanks!
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406

ADVERTISEMENT

Sorry, I meant to reference A18, thinking that you wanted to get rid of the intermediary cell B18. But it looks like this is not the case.
 

jgthtrotih

Well-known Member
Joined
Aug 28, 2009
Messages
568
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Domenic,

Sorry, I meant to reference A18, thinking that you wanted to get rid of the intermediary cell B18. But it looks like this is not the case.
you've reverenced the right cells what i mean is...how does your formula know to count or find capital letters?..and how does Armando's do the same?

Thanks!

Happy Holidays!
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,796
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

With Domenic's formula, you can only use F9 to "break it out" and then scratch your head over it...for a good long time, in my case :)

As far as I can see, there's three pieces to the formula - first, to use Row() and Index() to create a list of numbers from 1 to n, as long as the length of your string (very cool, I might add). Then, use Mid() to get the Ascii code of each character in your string, using the list above to process each character in turn. The final piece is simply an evaluation of the Ascii code of those characters - whether or not they fall in the range of "capital letter" codes - and again, very neat using 77.5 as a "magic number" such that the codes will all fall within the range -12.5 to 12.5. I suppose really a fourth bit is sumproduct() to add up "winners", 1 point each.

If you aren't used to sumproduct - start your investigation there as we assume you know how that works! And if you aren't used to evaluating formulas with F9, you'll find it is good for "looking into" the evaluation of the formula step by step - formula evaluation on the audit toolbar does the same thing.

With Armando's Formula, it depends on all the values in column B have 3 characters with 3 dots --though it is true of your test sample in all cases, it won't work if you tried I. M. H. O. or N. A. S. A. It's a simple matter of dividing the length of the string by 2, excluding spaces.
 
Last edited:

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Try...

=SUMPRODUCT(--(ABS(CODE(MID(B18,ROW(INDEX(A:A,1):INDEX(A:A,LEN(B18))),1))-77.5)<=12.5))
Here's how it works....

Every character you type into a cell has a numeric value. Depending on the character, the values range from 1 to 255. You can see a characters numeric value by using the CODE() function. Upper case and lower case letters have different values.

B18 = A
=CODE(B18) = 65

B18 = a
=CODE(B18) = 97

You're interested in counting the upper case letters in a string. The numeric values for the upper case letters A to Z are 65 to 90.

So, you need to test each character in the string to see if it's numeric value is from 65 to 90. To do that we start with the MID() function to step through the string 1 character at a time.

This does that:

ROW(INDEX(A:A,1):INDEX(A:A,LEN(B18)))

This produces an array of numbers from 1 to the total length of the string and are the starting position arguments to the MID function.

B18 = AT&T

B18 = 4 characters so:

MID(B18,ROW(INDEX(A:A,1):INDEX(A:A,LEN(B18))),1)
MID(B18,{1;2;3;4},1)

That means:
Start at the 1st character in B18 and return 1 character = A
Start at the 2nd character in B18 and return 1 character = T
Start at the 3rd character in B18 and return 1 character = &
Start at the 4th character in B18 and return 1 character = T

This array of individual characters is passed to the CODE function which returns the numeric code values:

CODE(MID(B18,{1;2;3;4},1))
CODE({"A";"T";"&","T"})
CODE({65;84;38;65})

Ok, so we have the code numbers. Now we need to test them to see if they're from 65 to 90. We're going to use a little trick to do this.

Remenber, the code values for the upper case letters from A to Z are 65 to 90. The median of 65 to 90 is 77.5. 77.5 is the exact middle point of 65 to 90. We're going to subtract 77.5 from each code value in:

CODE({65;84;38;65})
CODE({65;84;38;65})-77.5

65-77.5 = -12.5
84-77.5 = 6.5
38-77.5 = -39.5
65-77.5 = -12.5

{-12.5;6.5;-39.5;-12.5}

When 77.5 is subtracted from every upper case letter code from A to Z, the result will fall within the range -12.5 to 12.5. If we convert the subtracted code values to their absolute values then we can say that all the upper case letters from A to Z will have a value of <=12.5

ABS({-12.5;6.5;-39.5;-12.5})
ABS({12.5;6.5;39.5;12.5})
ABS({12.5;6.5;39.5;12.5})<=12.5

12.5 <= 12.5 = TRUE
6.5 <= 12.5 = TRUE
39.5 <= 12.5 = FALSE
12.5 <= 12.5 = TRUE

{TRUE;TRUE;FALSE;TRUE}

SUMPRODUCT(--({TRUE;TRUE;FALSE;TRUE}))

SUMPRODUCT works with numbers so we need to convert those logicals to numbers. The double unary is one way to do that.

--TRUE = 1
--FALSE = 0

=SUMPRODUCT({1;1;0;1})

SUMPRODUCT then sums up the array of 1s and 0s:

=SUMPRODUCT({1;1;0;1}) = 3

So:

B18 = AT&T

How many upper case letters are there in that string?

=SUMPRODUCT(--(ABS(CODE(MID(B18,ROW(INDEX(A:A,1):INDEX(A:A,LEN(B18))),1))-77.5)<=12.5))

=3
 
Last edited:

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Just to add...

That trick of subtracting 77.5 saves us from having to do something like this:

=SUMPRODUCT(--(CODE(MID(B18,ROW(INDEX(A:A,1):INDEX(A:A,LEN(B18))),1))>=65),--(CODE(MID(B18,ROW(INDEX(A:A,1):INDEX(A:A,LEN(B18))),1))<=90))

In this version we're literally testing that each character code is >=65 and <=90.

Also, the shorter formula can be easily modified to count the lower case letters. Just change the subtraction "factor" to 109.5.

=SUMPRODUCT(--(ABS(CODE(MID(B18,ROW(INDEX(A:A,1):INDEX(A:A,LEN(B18))),1))-109.5)<=12.5))
 
Last edited:

jgthtrotih

Well-known Member
Joined
Aug 28, 2009
Messages
568
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
xenou,

If you aren't used to sumproduct - start your investigation there as we assume you know how that works! And if you aren't used to evaluating formulas with F9, you'll find it is good for "looking into" the evaluation of the formula step by step - formula evaluation on the audit toolbar does the same thing.
ya have never really used sumproduct before so i will look into it.

With Armando's Formula, it depends on all the values in column B have 3 characters with 3 dots --though it is true of your test sample in all cases, it won't work if you tried I. M. H. O. or N. A. S. A. It's a simple matter of dividing the length of the string by 2, excluding spaces.
actully i have tested both I. M. H. O. and N. A. S. A. and they work perfectly fine.

#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!
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,638
Members
414,398
Latest member
dhune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top