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:
    381
    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:
    272
  3. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    427
    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:
    381
    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,233
    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,950
    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,233
    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,950
    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:
    427
    = CODE(character) <= 90
    serves to eliminate lower-case letters but
    = NOT( FLOOR( ABS(2*CODE(character)-155) - 1, 25) )
    positively identifies upper-case.
  11. VDS

    VDS Member

    Messages:
    381
    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
  12. p45cal

    p45cal Well-Known Member

    Messages:
    1,233
    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: Aug 21, 2018
  13. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    427
    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
    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: Aug 21, 2018

Share This Page