×

Does Cell Contain Upper Or Lower Case Character Formula

By Chris Newman •  Updated: 01/15/24 •  5 min read
How to create multiline text in ribbon tooltips with Excel.

Text Detection Formulas In Excel

Microsoft Excel is a powerhouse for anyone who loves to crunch numbers and juggle data. But it's not just about numbers; Excel's text manipulation capabilities are equally impressive and indispensable. Whether you're cleaning up a dataset for analysis or preparing a report, knowing how to determine if a cell contains an upper or lower case character can save you hours of manual work.

Screentip and Supertip example in Excel.

In this article, we'll be diving deep into the world of Excel "text detection" formulas to make your life easier. Let's get started!


Excel Formula To Check For Uppercase Letters

The following Excel formula will provide a TRUE result if a cell in your spreadsheet cell contains an uppercase letter. Simply point the cell reference to the cell you'd like to test.

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

A super tip text example in Excel's Ribbon.

How Does This Capital Letter Detector Formula Work?

LOWER(A1): This function converts all the letters in cell A1 to lowercase. If A1 contains "Excel", the LOWER(A1) function changes it to "excel".

EXACT(LOWER(A1),A1): The EXACT function compares two strings to check if they are exactly the same, taking into account letter case. It returns TRUE if the two strings match exactly, and FALSE otherwise. In this part of the formula, EXACT is comparing the result of LOWER(A1) with the original content of A1. If A1 was "excel", then LOWER(A1) would also be "excel", and EXACT would return TRUE. However, if A1 contained any uppercase letters (e.g., "Excel"), the comparison would be between "excel" (from LOWER(A1)) and "Excel" (the original A1), and EXACT would return FALSE because of the case difference.

NOT(EXACT(LOWER(A1),A1)): The NOT function simply inverts the boolean value returned by the EXACT function. If EXACT returns TRUE (meaning the original A1 and the lowercase-converted A1 are the same, implying A1 had no uppercase letters to begin with), NOT converts this TRUE to FALSE. Conversely, if EXACT returns FALSE (meaning there was a difference due to uppercase letters in A1), NOT converts this FALSE to TRUE.


Formula To Check For Lowercase Letters

The following Excel formula will provide a TRUE result if a cell in your spreadsheet cell contains a lowercase letter. Simply point the cell reference to the cell you'd like to test.

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

A super tip text example in Excel's Ribbon.

How Does This Lowercase Finder Formula Work?

UPPER(A1): This function converts all the letters in cell A1 to uppercase. For instance, if A1 contains "Excel", the UPPER(A1) function changes it to "EXCEL".

EXACT(UPPER(A1),A1): The EXACT function is used to compare two text strings to determine if they are exactly the same, including their case. It returns TRUE if the two strings are identical and FALSE otherwise. In this part of the formula, EXACT is comparing the result of UPPER(A1) (which is A1 converted to uppercase) with the original content of A1. If A1 was "EXCEL", then UPPER(A1) would also result in "EXCEL", making EXACT return TRUE. However, if A1 contained any lowercase letters (e.g., "Excel"), the comparison would be between "EXCEL" (from UPPER(A1)) and "Excel" (the original A1), and EXACT would return FALSE due to the mismatch caused by the lowercase letter.

NOT(EXACT(UPPER(A1),A1)): Finally, the NOT function is applied to invert the boolean value returned by the EXACT function. If EXACT returns TRUE (implying that the original A1 and the uppercase-converted A1 are identical, which would mean A1 had no lowercase letters), NOT flips this TRUE to FALSE. If EXACT returns FALSE (indicating a difference due to the presence of lowercase letters in A1), NOT changes this FALSE to TRUE.


Formula To Check If Proper Case Is Being Utilized [Bonus!]

The following Excel formula will provide a TRUE result if a cell in your spreadsheet cell's text is utilizing the "Proper Case" format.

"Proper Case" follows the sentence structure of capitalizing the first letter of every word in a given text. Microsoft can convert text into this format by feeding the text through the PROPER function.

The following formula will test Cell A1 to see if the text aligns with the Proper Case style:

=EXACT(PROPER(A1),A1)

Excel Formula Detect Proper Case

Enhance Your Text Case Detection Formula With Custom Results

If you would like to provide a result that is not simply TRUE or FALSE, you can add to this formula by incorporating an IF function.

Instead of True/False, let's utilize some symbols instead to indicate whether the cell contains an uppercase or lowercase letter. In this example, I'll utilize a technique to incorporate checkmarks and Xs as my result.

Cell Have Uppercase Letters?Cell Have Lowercase Letters?
=NOT(EXACT(LOWER(A1),A1))=NOT(EXACT(UPPER(A1),A1))
=IF(NOT(EXACT(LOWER(A1),A1)),"✔","")=IF(NOT(EXACT(UPPER(A1),A1)),"✔","")

I Hope This Microsoft Excel Article Helped!

Hopefully, I was able to explain how you can write Excel formulas to test a cell's text value for the existence of capital letters or lowercase characters. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.