The IF function doesn’t support wildcards directly, so we can’t use IF by itself.
Instead, we can combine the IF function with the COUNTIF function, which does support wildcards.
“, with the question mark (?)
representing any single character.
Because the range provided to COUNTIF is just one cell, the result will always be 1 or zero.
Inside the IF function, Excel will evaluate any non-zero number as TRUE and zero as FALSE.
When COUNTIF returns zero, IF will return “invalid”.
The values returned by IF can be customized as needed.
If SEARCH does not find a match, it returns a #VALUE!
The ISNUMBER function is used to convert the result from SEARCH into TRUE or FALSE.
Like COUNTIF, SEARCH supports wildcards, so we can use the same pattern to check for invalid codes.
For more details on how SEARCH and ISNUMBER can be used together, seethis page.
Note: The COUNTIF function has a limitation the range argumentmust be a range.
It’s not possible to pass anarrayfrom another function into COUNTIF.
More than one condition can be tested by nesting IF functions.
SEARCH returns the position offind_textinsidewithin_textas a number.
Related videos
The IF function
How to use the COUNTIF function
