• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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
 
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:
Top