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
