Data Validation & Hygiene
Data Validation: Reject Forbidden Substrings, Placeholders, or Disposable Domains
Block cell entries containing prohibited tokens such as "TBD", "test@", or disposable email domains case-insensitively using ISERROR(SEARCH(...)) rules.
Case-insensitive "must not contain" validation with SEARCH
SEARCH(forbidden_token, cell) returns a numeric character position whenever forbidden_token appears anywhere inside cell (ignoring letter case) and returns #VALUE! when absent. Wrapping each forbidden token in ISERROR(SEARCH(token, cell)) therefore returns TRUE only when the token is completely absent.
Combining LEN(TRIM(A2)) > 0 with ISERROR(SEARCH(...)) across a compact blocklist table prevents collaborators from submitting blank placeholders ("TBD", "TODO") or disposable test addresses in shared CRM intake sheets.
| Submitted Email / Note | Triggered Block Rule | Validation Passes? |
|---|---|---|
| ops@nordicfreight.eu | None (clean corporate email) | TRUE |
| test@nordicfreight.eu | Contains "test@" | FALSE |
| lead@mailinator.com | Contains "@mailinator.com" | FALSE |
| Owner TBD | Contains "tbd" (case-insensitive) | FALSE |
| Blank after TRIM | FALSE |
Multi-Token "Must Not Contain" Validation Formula
Requires non-empty trimmed text in A2 and verifies that none of the prohibited substrings in $D$2:$D$4 appear anywhere in A2.
=AND(LEN(TRIM(A2)) > 0, ISERROR(SEARCH($D$2, A2)), ISERROR(SEARCH($D$3, A2)), ISERROR(SEARCH($D$4, A2)))Count Clean Approved Entries vs. Blocked Submissions
Counts how many intake rows pass all blocklist checks in B2:B6.
=COUNTIF(B2:B6, TRUE)Try the example
Use the original values or change them to test your understanding.