You can use the following formulas to create an IF function with a wildcard in Excel:
Method 1: Check if Cell Contains Partial Text
=IF(COUNTIF(A2, "*hello*"),"Yes", "No")
This formula checks if cell A2 contains the text “hello” anywhere in the cell and returns “Yes” or “No” accordingly.
Method 2: Check if Cell Contains Specific Format
=IF(COUNTIF(A2,"??-???"),"Yes", "No")
This formula checks if cell A2 contains a format in which there are exactly two characters followed by a dash followed by exactly three characters, then returns “Yes” or “No” accordingly.
The following examples show how to use each formula in practice with the following list of employee ID’s for some company in Excel:
Example 1: Check if Cell Contains Partial Text
We can type the following formula into cell B2 to check if cell A2 contains the partial text “AB” anywhere in the employee ID:
=IF(COUNTIF(A2, "*AB*"),"Yes", "No")
We can then click and drag this formula down to each remaining cell in column B:
Column B returns either “Yes” or “No” to indicate if each employee ID contains “AB” or not.
For example:
- AB-009 contains AB so column B returns “Yes”
- AA-3345 does not contain AB so column B returns “No”
- AA-390 does not contain AB so column B returns “No”
And so on.
Example 2: Check if Cell Contains Specific Format
We can type the following formula into cell B2 to check if cell A2 has a format in which there are exactly two characters followed by a dash followed by exactly three characters:
=IF(COUNTIF(A2,"??-???"),"Yes", "No")
We can then click and drag this formula down to each remaining cell in column B:
Column B returns either “Yes” or “No” to indicate if each employee ID has the format that we specified.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Use IF Function with Text Values
Excel: How to Create IF Function to Return Yes or No
Excel: How to Use an IF Function with Range of Values
