Table of Contents
A wildcard in an IF function in Excel allows you to perform a specific task or calculation based on a certain condition. This feature allows you to replace part of a text string in a cell with a specified character or set of characters, making it useful for handling large amounts of data. By using a wildcard in the logical test of an IF function, you can easily search for and manipulate data that meets certain criteria, without having to manually input each individual value. This can save time and effort when working with large datasets and can make complex tasks more efficient and manageable.
Excel: Use an IF Function with Wildcard
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”
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: