Table of Contents
The “If Contains” formula in Excel is a simple, yet powerful tool that allows users to quickly and easily determine if a specific cell or range of cells contains a certain value or phrase. This formula is especially useful for data analysis, as users can quickly check if a certain value is present in a dataset. It can also be used to compare datasets or to search for specific information within a larger dataset. It is an invaluable tool for many Excel users, and is relatively simple to use.
You can use the following formula in Excel to determine if a cell contains a certain string:
=IF(ISNUMBER(SEARCH("this",A1)), "Yes", "No")
In this example, if cell A1 contains the string “this” then it will return a Yes, otherwise it will return a No.
The following examples show how to use this formula in practice.
Example: Check if Cell Contains a Certain String in Excel
Suppose we have the following dataset in Excel that shows the number of points scored by various basketball players:
We can use the following formula to check if the value in the Team column contains the string “mavs”:
=IF(ISNUMBER(SEARCH("mavs",A2)), "Yes", "No")
We can type this formula into cell C2 and then copy and paste it down to the remaining cells in column C:
The three rows with a value of “mavs” in the Team column all receive a Yes in the new column while all other rows receive a No.
Note that the SEARCH() function in Excel is case-insensitive.
If you’d like to perform a case-sensitive search, you can swap out the SEARCH() function in the formula with the FIND() function.
For example, we could use the following formula to check if any value in the Team column is equal to “MAVS”:
=IF(ISNUMBER(FIND("MAVS",A2)), "Yes", "No")
The following screenshot shows how to use this formula in practice:
Notice that every value in the new column is equal to No because none of the team names match the uppercase “MAVS” string that we specified in the formula.