The Problem
Promo codes, SKUs, and internal labels often follow an all-caps rule because uppercase text is easier to standardize across systems. 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. A proper audit should compare every entry against the same casing standard and return a label that is easy to count later.
The flow below shows the cleanup problem. The source list can contain clean uppercase entries, mixed-case entries, and codes with numbers, but the worksheet still needs one consistent rule for deciding what passes.
The Problem: Auditing Text Case Consistently A casing rule only helps when every row is checked against the same standard.
In this workbook, the entry strings are listed in one column and the audit result belongs beside each row. After the row labels are finished, the summary should show how many records were processed and how many entries passed the all-caps rule.
Code list -> row audit label -> compliance count
That keeps the cleanup practical. The user does not need to fix the text yet; first, the sheet needs to identify which rows already follow the standard and which rows need attention.
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.
In this challenge, it lets the sheet decide whether the original entry already matches the uppercase standard. Without a case-sensitive check, mixed-case text could slip through as if it were clean.
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.
That is helpful because the user does not need a separate reference table of approved versions. The formula can build the uppercase version of each entry on the fly.
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.
That turns the row-by-row cleanup into a quick compliance number, which is much easier to review than scanning every code manually.
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.