1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Ask an Excel Question' started by VDS, Aug 10, 2018.

  1. VDS

    VDS Member

    Messages:
    379
    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

    Attached Files:

  2. AliGW

    AliGW Active Member

    Messages:
    246
  3. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    286
    This is the direct, naïve approach of splitting the string and counting.
    Like the swan, the frantic paddling is out of sight!

    Attached Files:

  4. VDS

    VDS Member

    Messages:
    379
    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
  5. p45cal

    p45cal Well-Known Member

    Messages:
    1,156
    test:
    Code (vb):
    =IF(EXACT(UPPER(A1),A1),"Capitals",IF(EXACT(LOWER(A1),A1),"small","mixed"))
  6. Vijaykumar Shetye

    Vijaykumar Shetye New Member

    Messages:
    2
    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
  7. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,853
    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
  8. p45cal

    p45cal Well-Known Member

    Messages:
    1,156
    but won't the capital letter count include any numerals, spaces, parentheses and such like with that formula?
  9. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,853
    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
  10. Peter Bartholomew

    Peter Bartholomew Active Member

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

Share This Page