# How to calulate total number of Upper case and Lower Case Characters

#### VDS

##### Member
Dear Gentlemen,

Is there any function to know total number of lower case letters and upper case characters separately. I have attached sample file wherein the result has been taken separately.

But the result of lower case letters and upper case letters shouldbe taken without helper column.

How it can be done.

VDSA

#### Attachments

• 9.2 KB Views: 15

#### Peter Bartholomew

##### Well-Known Member
This is the direct, naïve approach of splitting the string and counting.
Like the swan, the frantic paddling is out of sight!

#### Attachments

• 11.9 KB Views: 10

#### VDS

##### Member
Nice replies. After studying, I have one query. If am typing APPLE in A1 and orange in A2, excel should return the result whether it is capital letters or small letters with any formula.

My suggestion. If the code of any character comes in between 65-90, it should be capital letters and ifthe code of any character comes in between 97-122,it should be small letters.

A1=====B1
APPLE- Capital
orange - small

Or any other way to get it done.

Help is requested.

VDS

#### p45cal

##### Well-Known Member
test:
Code:
``=IF(EXACT(UPPER(A1),A1),"Capitals",IF(EXACT(LOWER(A1),A1),"small","mixed"))``

#### Vijaykumar Shetye

##### New Member
Dear VDS,

Enter the following Array Formula in cell C6 for Lower Case count and drag it down.
=SUM(IFERROR(IF(CODE(MID(\$B6,ROW(1:20),1))>90,1,0),0))

Enter the following formula in cell D6 for Upper case count and drag it down.
=SUM(IFERROR(IF(CODE(MID(\$B6,ROW(1:20),1))<=90,1,0),0))

Array Formulas are entered using Control + Shift + Enter, instead of Enter.

The formulas will work correctly even if there are mixed Alphabets (partially Capital and partially Small).

As per your Example File, the data only contains alphabets. Hence, I have not considered numbers and special characters (spaces, symbols, unprintable characters).

Regards,

Vijaykumar Shetye,
Panaji, Goa, India

#### bosco_yip

##### Excel Ninja
Dear VDS,

Enter the following Array Formula in cell C6 for Lower Case count and drag it down.
=SUM(IFERROR(IF(CODE(MID(\$B6,ROW(1:20),1))>90,1,0),0))

Enter the following formula in cell D6 for Upper case count and drag it down.
=SUM(IFERROR(IF(CODE(MID(\$B6,ROW(1:20),1))<=90,1,0),0))

Array Formulas are entered using Control + Shift + Enter, instead of Enter.

The formulas will work correctly even if there are mixed Alphabets (partially Capital and partially Small).

As per your Example File, the data only contains alphabets. Hence, I have not considered numbers and special characters (spaces, symbols, unprintable characters).

Regards,

Vijaykumar Shetye,
Panaji, Goa, India
If the OP's data all in Capital letter or Small letter

This 2 simple formulas return the same result

1] For small letter count :

=(CODE(A1)>90)*LEN(A1)

2] For capital letter count :

=(CODE(A1)<=90)*LEN(A1)

Regards
Bosco

#### p45cal

##### Well-Known Member
but won't the capital letter count include any numerals, spaces, parentheses and such like with that formula?

#### bosco_yip

##### Excel Ninja
but won't the capital letter count include any numerals, spaces, parentheses and such like with that formula?
Hi, p45cal

Agreed with your point, since the OP gave the example too simply

My reply just point out Vijaykumar's formula in post #.6 too complicated

Regards
Bosco

#### Peter Bartholomew

##### Well-Known Member
= CODE(character) <= 90
serves to eliminate lower-case letters but
= NOT( FLOOR( ABS(2*CODE(character)-155) - 1, 25) )
positively identifies upper-case.

#### VDS

##### Member
Dear All,

Great Work. Brilliant aswers................it is too wonderful.

Is it can be clubbed into conditional formatting and highlight cell as per user requirement

VDS

#### p45cal

##### Well-Known Member
= NOT( FLOOR( ABS(2*CODE(character)-155) - 1, 25) )
positively identifies upper-case.
It shouldn't be too difficult to adjust this for lower case, but it's not immediateley obvious (219 instead of 155?).
If you're going down that route:
=INT((CODE(character)-65)/26)=0
does the same thing and it's quite transparent to adjust it for lower case:
=INT((CODE(character)-97)/26)=0

@VDS, you've had several answers, each giving different results in various circumstances; you haven't made it clear what you want (your thread title mentions counting a total number - this is difficult to incorporate into conditional formatting since conditional formatting with formulae wants to see TRUE/FALSE).
So…

Last edited:

#### Peter Bartholomew

##### Well-Known Member
The formula I used was extracted from an even more obscure version that tested for upper or lower case letters simultaneously. The idea is to test for both upper and lower bounds at a single step by testing how far a value is from the average of the bounds.

I agree it is over-complicated and that
=INT((CODE(character)-65)/26)=0
wins out when it comes to transparency.

I think it may be better still to merge the two concepts to give
= NOT(FLOOR(CODE(character)-65, 26))
which only requires integer arithmetic.

Last edited: