Explanation
The goal of this formula is to verify whether the values in column B follow the format xx-xxxx-xxx, where "x" represents any single character. 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.
Excel wildcards
Excel supports three wildcards that can be used in formulas:
- Asterisk (*) - zero or more characters
- Question mark (?) - any one character
- Tilde (~) - escape for literal character (~*) a literal question mark (~?), or a literal tilde (~~).
IF + COUNTIF
Unlike several other frequently used functions, the IF function does not support wildcards. However, you can use the COUNTIF or COUNTIFS functions inside the logical test of IF for basic wildcard functionality. In the example shown, the formula in D5 is:
=IF(COUNTIF(B5,"??-????-???"),"ok","invalid")
Working from the inside out, the logical test inside the IF function is based on the COUNTIF function:
COUNTIF(B5,"??-????-???") // returns 1 or 0
In this context, COUNTIF counts cells matching the pattern "??-????-???", 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 the number 1 (indicating that the value in B5 consists of 11 characters with two hyphens as described by the pattern), IIF interprets this as TRUE and returns "ok". When COUNTIF returns zero, IF will return "invalid". The values returned by IF can be customized as needed.
IF + SEARCH
Another way to use wildcards with the IF function is to combine the SEARCH and ISNUMBER functions to create a logical test. This works because the SEARCH function supports wildcards:
=IF(ISNUMBER(SEARCH("??-????-???",B5)),"ok","invalid")
The SEARCH function returns the position of a match inside a text string. If SEARCH does not find a match, it returns a #VALUE! error. 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, see this page.
Note: The COUNTIF function has a limitation – the range argument must be a range. It's not possible to pass an array from another function into COUNTIF. If you run into this problem, you can use SEARCH + ISNUMBER option above instead.
Related formulas
If cell contains
If cell is this OR that
Related functions
IF Function
The Excel IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical...
COUNTIF Function
The Excel COUNTIF function returns the count of cells in a range that meet a single condition. The generic syntax is COUNTIF(range, criteria), where "range" contains the cells to count, and "criteria" is a condition that must be true for a cell to be counted. COUNTIF can be used to count cells...
SEARCH Function
The Excel SEARCH function returns the location of one text string inside another. SEARCH returns the position of find_text inside within_text as a number. SEARCH supports wildcards, and is not case-sensitive....
ISNUMBER Function
The Excel ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. You can use ISNUMBER to check that a cell contains a numeric value, or that the result of another function is a number.
Related videos
The IF function
