Find uppercase or lowercase letters in Excel cells

Finding named entities is a task in many professions. It comes up wherever text contains proper names: brands, people’s names, places, organizations and other names of living and non-living things.

They all follow one simple rule: they start with a capital letter. This also makes capitalization a marker that helps you pick them out from the rest of the text.

With large datasets, it’s often better to first find the cells that contain such names, and only then do the “heavy” operations:

• bulk replacement of characters or words;
• extraction of text with capital letters, whether words or entire cells;
• deletion of characters, words, phrases or entire cell contents.

Finding uppercase English letters

Excel has the FIND function, and it’s case-sensitive, so it finds letters exactly in the case they are listed in. Combined with an array, it returns the position of any found character.

Since uppercase English letters go in a row in the ASCII table, from 65 to 90, you can use a compact array formula instead of listing all 26 letters:

=COUNT(FIND(CHAR(ROW($65:$90)),A1))>0

The formula returns TRUE if cell A1 contains at least one uppercase English letter. Use the fixed range $65:$90: otherwise, when you copy the formula to cells above or below, the range of letters shifts.

If rows may be inserted inside or above rows 65–90 of the sheet, use a text reference with INDIRECT, which nothing can shift:

=COUNT(FIND(CHAR(ROW(INDIRECT("65:90"))),A1))>0

How it works step by step:

• INDIRECT("65:90") creates a reference to rows 65 to 90;
• ROW(…) returns an array of numbers from 65 to 90, the ASCII codes of A–Z;
• CHAR(…) turns the codes into the letters A, B, C… Z;
• FIND(…,A1) looks for each capital letter in A1 and returns its position or an error;
• COUNT(…) counts the numbers, that is, successful searches, and ignores the errors;
• >0 returns TRUE if at least one capital letter was found.

In Excel 2019 and earlier, enter it as an array formula: type it without curly braces and press Ctrl+Shift+Enter, the braces appear by themselves. In Excel 365 and Excel 2021, just press Enter. More shortcuts are in the list of Excel hotkeys.

In Excel 365, the built-in REGEXTEST function does the same. It’s case-sensitive by default:

=REGEXTEST(A1,"[A-Z]")

Finding any capital letters

Besides English, there are other alphabets and letters with diacritics: É, Ü, Ñ, Ø. We can’t list them all, the formulas would be huge and slow.

Logic helps here, namely the LOWER and EXACT functions, the latter being case-sensitive. The logic is:

• convert the value to lowercase;
• compare the result with the original;
• if they don’t match, at least one character was uppercase.

Here is the formula:

=NOT(EXACT(LOWER(A1),A1))

It works for any language, because it doesn’t list letters, but checks whether the text changes when converted to lowercase.

Finding any lowercase letters

The formula is the same as for uppercase. The only difference is that it uses the UPPER function:

=NOT(EXACT(UPPER(A1),A1))

Other checks with letter case

The same building blocks answer a few more questions. Is the whole cell in caps (and has at least one letter)?

=AND(EXACT(UPPER(A1),A1),NOT(EXACT(LOWER(A1),A1)))

Does the cell start with a capital letter?

=AND(EXACT(LEFT(A1,1),UPPER(LEFT(A1,1))),NOT(EXACT(LEFT(A1,1),LOWER(LEFT(A1,1)))))

How many uppercase English letters are in the cell? SUBSTITUTE is case-sensitive, so it removes only capitals, and the difference in length is their count:

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW($65:$90)),"")))

“Find capital letters” tool in !SEMTools

So that you don’t have to remember the syntax of compound formulas, there are ready-made solutions. !SEMTools has a set of them, including a procedure that detects capital letters.

It’s on the !SEMTools tab, under FIND → Characters → Uppercase. The steps are simple:

• copy the source column to the next column;
• with the copy still selected, choose FIND → Characters → Uppercase.

Cells with capital letters turn into TRUE, the rest into FALSE, right in place. Then you can filter, sort, copy and do anything else with the marked data. No formulas to type or fill down.

Summary: finding uppercase and lowercase letters

I hope you found what you were looking for on this page and were able to find the uppercase or lowercase letters in your cells. For English letters, COUNT(FIND(CHAR(ROW(…)))) works in any version, and the EXACT + LOWER pair works for any language. For whole columns, the !SEMTools procedure is the fastest: select, click, done.

You may also be interested in related operations: regular expressions in Excel to find, replace and extract by pattern, and how to change letter case in Excel.