How to Count Uppercase Letters in Excel

This guide will show you how to count uppercase letters in Excel. Counting the number of capital letters or uppercase letters in an Excel cell can be tricky because the Microsoft Excel doesn’t have a function just for this, but you can still do it by combining a few functions.

Steps: How to Count Uppercase Letters in Excel Formula?

Let’s understand how to count uppercase letters in Excel formula step by step.

Step 1: Learn the Formulas

• First, learn the below formulas which will be used to count the number of uppercase letters in Excel.
• Then it will be easy to understand the combination of these formula to count capital letters in Excel.

Step 2: Enter Sample Text

• Copy the below sample data and paste in cell A1 of a new sheet in Excel.
• Or enter any text in few cells with combination of uppercase and lowercase letters.

Step 3: Use the Formula

• Copy the below formula and paste in cell B2 to count the uppercase letters in cell A2.
• Drag the formula down from B2 to B7 to count for all cells in A1 column.

=SUMPRODUCT(LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(ROW(INDIRECT(“65:90″))),””)))

Example:

In cell A2 there are 5 uppercase letter (C, U, L, M and E) which is calculated using the formula in cell B2. Similarly, the capital letters in cell A3 to A7 are calculated and shown in cell B3 to B7.

Conclusion

Counting uppercase letters in Excel is simple using a mix of formulas and functions. By combining LEN, SUBSTITUTE, CHAR, ROW and INDIRECT functions, you can make a formula that counts uppercase letters in a cell or range. The formula shown in this tutorial is handy for identifying uppercase text and analysing the formatting in Microsoft Excel. You can also count the number of lowercase letters in Excel using the formulas and functions.

Learn More