Finding named entities is a common task across many professions. Whenever you’re working with text that includes proper nouns—such as brand names, people’s names, place names, organizations, and other entities—it’s important to identify them efficiently.
All of these entities follow a simple rule: they start with a capital letter. This capitalization serves as a marker, helping you distinguish such elements from the rest of the text.
When dealing with large datasets, it’s often useful to first locate the cells that contain such names before performing more advanced operations like:
- mass replacing characters or words,
- extracting capitalized text (words or full cell values),
- removing certain characters, words, phrases, or entire contents.
Finding uppercase Latin characters
Excel includes a FIND function that’s case-sensitive, making it useful for detecting uppercase letters. Combined with an array formula, it can return the position of any capital letter from a predefined list.
Since uppercase English letters correspond to ASCII values 65 through 90, you can use this array formula to find them efficiently:
=COUNT(FIND(CHAR(ROW(65:90));A1))>0
This formula returns TRUE if the cell contains at least one uppercase Latin letter.
Note: In Excel 365 and Excel 2021, you can simply press Enter — the formula will work as a dynamic array automatically. In older versions of Excel, however, you must press Ctrl + Shift + Enter to enter it as an array formula (you’ll see curly braces appear around it).
Finding Any Capital Letters
When working with different alphabets (not just Latin), listing all uppercase characters manually becomes inefficient.
Instead, use a logic-based approach with the LOWER and EXACT functions. EXACT is case-sensitive and allows comparing the original cell to its lowercase version.
- Convert the cell content to lowercase,
- Compare the result with the original value,
- If the two don’t match, it means the original had uppercase letters.
Here’s the formula:
=NOT(EXACT(LOWER(A1);A1))
Finding any lowercase letters
To check for lowercase letters instead, simply reverse the logic using the UPPER function:
=NOT(EXACT(UPPER(A1);A1))
“Find capital letters” Tool in !SEMTools
Rather than memorizing formulas, you can use the built-in solution from the !SEMTools add-in. It includes a macro specifically for detecting uppercase letters.
You’ll find the tool under the “Symbols” menu → “Detect” group → “Capital Letters.” It works as follows:
- Copy the original column of data to an adjacent column,
- While the copied column is still selected, run the procedure via the menu.
Cells that contain uppercase letters will be marked with TRUE, while others will return FALSE. You can then filter, sort, or process this data however you need.
This post is also available in RU.