View original (French)

Count the number of uppercase letters in a cell

Solved
rorocr Posted messages 82 Registration date   Status Member Last intervention   -  
 Tetris -
Hello,

I would like to know if there is a formula or a trick to count the number of uppercase letters in a cell?

Thank you in advance,

5 answers

  1.
    Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hello,

    if the number of uppercase letters is limited to just a few, you can use this formula

    =SUMPRODUCT((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)={"A","B","C","D","E","I","O","U","Y"})*1)

    however, if all the letters of the alphabet are involved, you will need to complete the formula.
    I will see if it's possible to make it simpler

    --
    A+
    Mike-31

    I am responsible for what I say, not for what you understand...
    2
  2. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hi,

    Here is additional information without defining uppercase letters
    =SUMPRODUCT((CODE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*1>=65)*(CODE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*1<=90)*(CODE((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*1<>"")))


    There might be a shorter way

    See you,
    Mike-31

    I am responsible for what I say, not for what you understand...
    1
  3. jeannets Posted messages 28396 Registration date   Status Contributor Last intervention   Ambassadeur 6 603
     
    Hello,

    I don't have the solution, but I can give you a hint for the detection

    the binary code for uppercase letters starts with 4 or 5
    and for lowercase letters, it starts with 6 or 7

    and viewed in binary weight, (1-2-4-8) there is the bit 4+1 or 4 alone in uppercase and nothing else
    and the bit 4+2 or 4+2+1 in lowercase

    The "A" = 45 and the "a" = 65 for example (in Hexadecimal)

    there you go, that might help, but I don't have anything else.
    0
  4. rorocr Posted messages 82 Registration date   Status Member Last intervention   1
     
    Hello Mike-31, and thank you for your response,

    it works perfectly because I don't have many uppercase letters available.
    0
  6. Tetris
     

    SUM(LEN(IFERROR(REGEX.EXTRACT(A2,"[A-Z]",1,0),)))

    with A2 as the cell from which we want to count the number of uppercase letters

    By analogy:

    SUM(LEN(IFERROR(REGEX.EXTRACT(A2,"[a-z]",1,0),)))

    for the number of lowercase letters

    0