Data Validation & Hygiene
Data Validation: Require Cells to Contain a Specific Substring (SEARCH vs. FIND)
Enforce mandatory ticket prefixes, domain tags, or currency codes inside text inputs using ISNUMBER(SEARCH(...)) for case-insensitive rules or ISNUMBER(FIND(...)) for exact-case tokens.
How ISNUMBER + SEARCH/FIND converts character offsets into boolean gates
SEARCH(find_text, within_text) returns the 1-based starting position when a substring appears anywhere inside a cell and #VALUE! when absent. Wrapping the call in ISNUMBER(...) converts every numeric match position into TRUE and every #VALUE! miss into FALSE—exactly what Data Validation expects.
Choose SEARCH when casing may vary (e.g., "@arte-soft.com" matching "@ARTE-SOFT.COM"), and choose FIND when uppercase tokens like "PO-" must reject lowercase "po-"—plus remember that FIND treats "?" and "*" as literal characters rather than wildcards.
| Candidate Input | Required Token ($D$2 = "PO-") | Case-Insensitive (SEARCH) | Case-Sensitive (FIND) |
|---|---|---|---|
| "Vendor PO-4082" | "PO-" | TRUE | TRUE |
| "vendor po-4082" | "PO-" | TRUE | FALSE |
| "INV-4082" | "PO-" | FALSE | FALSE |
| (Blank cell) | "PO-" | FALSE | FALSE |
Case-Insensitive Required Substring Rule
Accepts any non-blank cell containing $D$2 regardless of uppercase/lowercase spelling.
=AND(NOT(ISBLANK(A2)), ISNUMBER(SEARCH($D$2, A2)))Exact-Case Required Substring Rule
Replaces SEARCH with FIND so lowercase "po-" is rejected when uppercase "PO-" is required.
=AND(NOT(ISBLANK(A2)), ISNUMBER(FIND($D$2, A2)))Try the example
Use the original values or change them to test your understanding.