Search code examples
charformulauppercasestring-lengthopenoffice-calc

How to count by formula the length of the uppercase letters of a cell in Apache OpenOffice Calc?

The following formula converts the characters to upper case and then calculates the number of all upper case letters.

tesT                              // A1
=LEN(SUBSTITUTE(UPPER(A1);"";"")) // A2: 4 instead of 1
Solution

  • The UPPER function converts lower case to upper case, so it won't work this way. Sadly, i fear there's no solution for Apache OpenOffice. However, you could do this with LibreOffice Calc, using the REGEX function:

    =LEN(REGEX(A1;"[a-z0-9]";"";"g"))

    removes all lower-case characters and numbers, assuming there are no other text content (special characters or something like that). However, a bullet-proof solution would require writing a custom function.