Data Validation & Hygiene
Data Validation: Allow Letters and Numbers Only Without Punctuation or Spaces
Enforce strict ASCII alphanumeric input (A–Z, a–z, 0–9) with zero hyphens, spaces, or punctuation marks using an auditable CODE and MID character-range check.
Why ISTEXT and ISNUMBER cannot validate mixed alphanumeric codes
Warehouse bin IDs, coupon codes, and ERP batch tokens such as BIN408A or PROMO2026 are text strings, so ISTEXT returns TRUE even when a user accidentally enters spaces (USER 09), hyphens (QA-104), or symbols (VIP#1).
Inspecting each character position with CODE(UPPER(MID(code, pos, 1))) verifies that every occupied character falls strictly inside ASCII digits 48–57 (0–9) or uppercase letters 65–90 (A–Z), while requiring LEN(code) between 1 and the maximum token width.
| Candidate Token | Length | Violating Character | Strict Alphanumeric? |
|---|---|---|---|
| BIN408A | 7 | None (digits & letters only) | TRUE |
| promo2026 | 9 | None (case-insensitive A–Z / 0–9) | TRUE |
| QA-104 | 6 | Hyphen "-" (ASCII 45) | FALSE |
| USER 09 | 7 | Space " " (ASCII 32) | FALSE |
| VIP#1 | 5 | Hash "#" (ASCII 35) | FALSE |
Strict 1-to-10 Character ASCII Alphanumeric Validation Rule
Returns TRUE only when A2 is non-blank, 1 to 10 characters long, and every character in A2 is an ASCII letter or digit with no spaces or punctuation.
=LET(txt, A2, n_len, LEN(txt), AND(NOT(ISBLANK(A2)), n_len >= 1, n_len <= 10, IF(1 > n_len, TRUE, LET(ch, CODE(UPPER(MID(txt, 1, 1))), OR(AND(ch >= 48, ch <= 57), AND(ch >= 65, ch <= 90)))), IF(2 > n_len, TRUE, LET(ch, CODE(UPPER(MID(txt, 2, 1))), OR(AND(ch >= 48, ch <= 57), AND(ch >= 65, ch <= 90)))), IF(3 > n_len, TRUE, LET(ch, CODE(UPPER(MID(txt, 3, 1))), OR(AND(ch >= 48, ch <= 57), AND(ch >= 65, ch <= 90)))), IF(4 > n_len, TRUE, LET(ch, CODE(UPPER(MID(txt, 4, 1))), OR(AND(ch >= 48, ch <= 57), AND(ch >= 65, ch <= 90)))), IF(5 > n_len, TRUE, LET(ch, CODE(UPPER(MID(txt, 5, 1))), OR(AND(ch >= 48, ch <= 57), AND(ch >= 65, ch <= 90)))), IF(6 > n_len, TRUE, LET(ch, CODE(UPPER(MID(txt, 6, 1))), OR(AND(ch >= 48, ch <= 57), AND(ch >= 65, ch <= 90)))), IF(7 > n_len, TRUE, LET(ch, CODE(UPPER(MID(txt, 7, 1))), OR(AND(ch >= 48, ch <= 57), AND(ch >= 65, ch <= 90)))), IF(8 > n_len, TRUE, LET(ch, CODE(UPPER(MID(txt, 8, 1))), OR(AND(ch >= 48, ch <= 57), AND(ch >= 65, ch <= 90)))), IF(9 > n_len, TRUE, LET(ch, CODE(UPPER(MID(txt, 9, 1))), OR(AND(ch >= 48, ch <= 57), AND(ch >= 65, ch <= 90)))), IF(10 > n_len, TRUE, LET(ch, CODE(UPPER(MID(txt, 10, 1))), OR(AND(ch >= 48, ch <= 57), AND(ch >= 65, ch <= 90))))))Count Valid Alphanumeric Codes Across an Intake Column
Once each row evaluates its boolean validation flag in B2:B6, COUNTIF(B2:B6, TRUE) reports how many rows pass clean-import gating.
=COUNTIF(B2:B6, TRUE)Try the example
Use the original values or change them to test your understanding.