Logical
Validate an Invoice Prefix, Four Digits, Allowlist and Blocklist
Audit invoice IDs as uppercase INV- plus four ASCII digits, reject reserved IDs and require literal membership in a configured uppercase currency allowlist.
Define the accepted format precisely
This example accepts exactly INV- followed by four ASCII digits, such as INV-2045. The hyphen is required punctuation; the suffix allows digits only. Leading zeroes are preserved because the identifier is text.
Currency codes must be nonempty uppercase text listed in E2:E4. This is a configured allowlist, not a complete ISO currency database. Maintain its entries in uppercase. The F2:F3 blocklist contains reserved invoice IDs.
D2 calculates currency validity; D3 combines it with invoice validation. These cells are audit results. Merely placing a formula in a worksheet does not automatically prevent entry, reject an import or configure a data-validation rule.
COUNTIF criteria normally interpret *, ? and comparison prefixes specially. The formulas escape tildes first, then wildcard characters, and prefix the criterion with = for literal equality. Uppercase input is checked separately because COUNTIF text matching is case-insensitive.
This format check is not CSV escaping or protection from formula injection. Export and import code must still handle quoting, delimiters and untrusted cell contents appropriately.
Literal allowlist membership in D2
Returns FALSE for empty, nontext, lowercase or unlisted values. A wildcard supplied as the code does not mean any approved code.
=IF(NOT(ISTEXT(B2)),FALSE,AND(LEN(B2)>0,EXACT(B2,UPPER(B2)),COUNTIF($E$2:$E$4,"="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"~","~~"),"*","~*"),"?","~?"))>0))Complete invoice audit in D3
Removing each ASCII digit from the four-character suffix must leave an empty string. This excludes letters, spaces and other punctuation rather than checking only a comma and exclamation mark.
=IF(NOT(AND(ISTEXT(A2),ISTEXT(B2))),FALSE,AND(EXACT(LEFT(A2,4),"INV-"),LEN(A2)=8,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RIGHT(A2,4),"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9","")="",COUNTIF($F$2:$F$3,"="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"~","~~"),"*","~*"),"?","~?"))=0,D2))Try the example
Use the original values or change them to test your understanding.