Text
Check if a cell begins or ends with specific text (prefix and suffix)
Test dynamic prefixes with LEFT(text, LEN(prefix)) and suffixes with RIGHT(text, LEN(suffix)), with optional EXACT case sensitivity.
Dynamic prefix and suffix checks without hardcoded lengths
Instead of hardcoding LEFT(A2, 4) = "INV-", measure the prefix cell dynamically with LEFT(A2, LEN(B2)) = B2 so changing the prefix from "INV-" to "PO-" requires zero formula edits.
Standard = comparison in spreadsheets is case-insensitive ("inv-" equals "INV-"). Wrap both sides in EXACT(...) when lowercase prefixes should be rejected.
Require nonempty prefix and suffix cells. The COUNTIF variant interprets *, ? and ~ as wildcard syntax, so escape them for literal prefixes; LEFT/RIGHT comparisons do not use wildcard syntax.
| InvoiceRef (A) | Prefix (B) | Suffix (C) | Prefix (= / EXACT) | Suffix Match |
|---|---|---|---|---|
| INV-2026-EU | INV- | -EU | TRUE / TRUE | TRUE |
| inv-2026-US | INV- | -EU | TRUE / FALSE | FALSE |
Prefix, case-sensitive prefix, and suffix formulas
Across ranges, COUNTIF(A2:A3, B2 & "*") counts all cells starting with Prefix case-insensitively (2 rows).
=LEFT(A2, LEN(B2)) = B2Case-sensitive EXACT prefix and RIGHT suffix checks
EXACT(LEFT(A3, LEN(B3)), B3) rejects lowercase "inv-", while RIGHT(A2, LEN(C2)) = C2 confirms "-EU".
=EXACT(LEFT(A3, LEN(B3)), B3)Try the example
Use the original values or change them to test your understanding.