Table of Contents
A simple Excel formula for “If Contains” is a logical function that allows you to check if a cell contains a specific text or value. This formula evaluates a cell and returns a specific value if the cell contains the specified text or value, and a different value if it does not. It is commonly used for data analysis and manipulation in spreadsheets, allowing users to quickly identify and categorize data based on specific criteria. This formula is helpful for organizing and sorting large amounts of data, making it easier to analyze and draw insights from.
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:
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:
Cite this article
stats writer (2024). What is a simple Excel formula for “If Contains”?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/what-is-a-simple-excel-formula-for-if-contains/
stats writer. "What is a simple Excel formula for “If Contains”?." PSYCHOLOGICAL SCALES, 1 Jul. 2024, https://scales.arabpsychology.com/stats/what-is-a-simple-excel-formula-for-if-contains/.
stats writer. "What is a simple Excel formula for “If Contains”?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/what-is-a-simple-excel-formula-for-if-contains/.
stats writer (2024) 'What is a simple Excel formula for “If Contains”?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/what-is-a-simple-excel-formula-for-if-contains/.
[1] stats writer, "What is a simple Excel formula for “If Contains”?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, July, 2024.
stats writer. What is a simple Excel formula for “If Contains”?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.