Data Cleaning & Text
Count & Audit Cells Containing Exactly N Characters (REPT "?" & LEN)
Count text codes matching an exact character length N using COUNTIF(range, REPT("?", N)) and flag truncated or padded SKU/IBAN entries row by row with LEN(TRIM(cell)).
Catch truncated or extra-character codes in imported catalogs!
Warehouse SKUs, country IBANs, GL account codes, and serial numbers follow strict fixed-width formats (for example, exactly 8 characters like "SKU-1042"). In COUNTIF, each question-mark wildcard "?" matches any single character, so REPT("?", N) generates a pattern of exactly N question marks ("????????") that counts every text cell of length N in one step!
Pair the column count with a row-level LEN(TRIM(cell)) = N check to distinguish genuine length errors ("SKU-88" = 6 chars) from accidental leading/trailing spaces.
| Imported SKU | Character Length | Matches N = 8? |
|---|---|---|
| SKU-1042 | 8 | Valid (1) |
| SKU-88 | 6 | Length Mismatch |
| SKU-2091 | 8 | Valid (2) |
| SKU-33045 | 9 | Length Mismatch |
| SKU-7710 | 8 | Valid (3) |
Count Text Cells with Exact Character Length N
Uses REPT("?", B7) inside COUNTIF to count how many text codes in A2:A6 have exact length B7.
=IF(B7 <= 0, "Invalid Length", COUNTIF(A2:A6, REPT("?", B7)))Row-Level Fixed-Width Code Audit (Row A3)
Checks whether trimmed code A3 equals target length B7.
=IF(B7 <= 0, "Invalid Length", IF(LEN(TRIM(A3)) = B7, "Valid", "Length Mismatch"))Try the example
Use the original values or change them to test your understanding.