Excel: A Simple Formula for “If Contains”


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:

Excel if contains example

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.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Count Duplicates in Excel
How to Count Frequency of Text in Excel
How to Calculate Average If Cell Contains Text in Excel

Leave a Reply

Your email address will not be published.