The Problem
Promo codes, SKUs, and internal labels often follow an all-caps rule. The problem is that lists rarely stay clean for long. A few mixed-case entries slip in, and then someone has to scan row by row to find them.
That manual check is easy on five rows and annoying on five hundred. It also gets unreliable fast because numbers and letters can look similar when you are moving quickly. This challenge gives us a simple way to audit the list and count how many entries really follow the rule.
How We Solve It
The safest pattern is to turn each value into uppercase and compare it to the original text with EXACT. If both versions match exactly, the entry is all caps. If not, we label it as mixed.
Method 1: Compare with UPPER
Method 1: Compare the original text to its uppercase version with EXACT.
This is the method the challenge wants. UPPER creates the all-caps version of the text, and EXACT checks whether the original already looks like that. Wrapping the result in IF lets us return clear labels instead of raw TRUE or FALSE values.
This solves the main audit problem because the worksheet needs to tell whether each code already follows the all-caps rule. By comparing the entry to its uppercase version, the formula can label the row without anyone having to inspect the text manually.
=IF(EXACT(A2, UPPER(A2)), "ALL CAPS", "MIXED")
Method 2: Check for all lowercase
Method 2: Check whether an entry is fully lowercase.
Sometimes the useful question is the opposite one. Instead of looking for approved all-caps entries, you may want to find rows that were typed fully in lowercase. The same structure works, but this time you compare the value to LOWER(A2).
This solves a different casing problem, where the sheet needs to detect entries that lean entirely the other way. It is useful when the cleanup rule changes from enforcing uppercase to diagnosing how the text was entered.
=IF(EXACT(A2, LOWER(A2)), "LOWER", "MIXED")
Method 3: Avoid a numbers-only false positive
Method 3: Add a second check so numbers-only text does not get mislabeled.
A value like 12345 matches both its uppercase and lowercase versions because there are no letters to change. If your real worksheet mixes codes and pure numbers, add a second test so only text that behaves like uppercase text is marked as all caps.
This solves an edge case that can make the audit misleading. It separates true uppercase text from values that only look acceptable because they contain no letters at all, which keeps the final label more trustworthy.
=IF(AND(EXACT(A2, UPPER(A2)), NOT(EXACT(A2, LOWER(A2)))), "ALL CAPS", "MIXED")
Function Explanation
1. EXACT
EXACT compares two text values and respects case. That detail matters here because a normal equality check would treat uppercase and lowercase letters as the same. This function is what makes the audit trustworthy.
Learn more this functionEXACT
2. UPPER
UPPER converts letters to uppercase without changing the rest of the string. We use it to create the standard we want to compare against, which keeps the formula short and easy to copy down.
Learn more this functionUPPER
3. COUNTIF
COUNTIF gives us the summary at the end. After column B is filled with labels, it counts how many rows say ALL CAPS so the audit result is easy to read.
One small gotcha is that punctuation and numbers stay the same when you use UPPER. That is usually fine for promo codes, but if your sheet includes entries with no letters at all, add the extra guard from Method 3.
Audit the code list and mark each entry as ALL CAPS or MIXED. Then finish the two summary cells so you can see how many records were checked and how many passed the casing rule.