Table of Contents
To check if a cell in Excel contains text from a list, follow the steps below:
1. Create a list of text values that you want to check for in a cell.
2. In the cell where you want to perform the check, use the formula =ISNUMBER(MATCH(A1, list, 0)).
3. Replace “A1” with the cell reference that you want to check and “list” with the range of cells containing the text values.
4. If the cell contains any text from the list, the formula will return “TRUE”. If not, it will return “FALSE”.
This method allows for a quick and efficient way to determine if a cell contains text from a predefined list in Excel. It is useful for data validation and ensuring accuracy in data entry.
Excel: Check if Cell Contains Text from List
You can use the following formula in Excel to check if a cell contains text from a list:
=IF(OR(COUNTIF(A1,"*"&$E$2:$E$8&"*")), "Yes", "No")In this example, if cell A1 contains any of the text values in the range E2:E8 then it will return a Yes, otherwise it will return a No.
The following example shows how to use this formula in practice.
Example: Check if Cell Contains Text from List in Excel
Suppose we have the following dataset in Excel that shows the number of points scored by various basketball players:
There are three teams in the list from Texas: Mavs, Spurs, and Rockets.
Suppose we’d like to create a new column that tells us whether each team is from Texas or not.
First, we’ll create a list of the Texas teams in column E:
Then we’ll use the following formula to check if the value in the Team column contains any of the text values in column E:
=IF(OR(COUNTIF(A2,"*"&$E$2:$E$4&"*")), "Yes", "No")We can type this formula into cell C2 and then copy and paste it down to the remaining cells in column C:
Notice that any row that contains Mavs, Spurs, or Rockets receives a value of Yes while all other rows receive a value of No.
Also note that we could return values other than “Yes” and “No.”
=IF(OR(COUNTIF(A2,"*"&$E$2:$E$4&"*")), "Texas", "Not Texas")The following screenshot shows how to use this formula in practice:
Notice that any row that contains Mavs, Spurs, or Rockets receives a value of Texas while all other rows receive a value of Not Texas.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I check if a cell contains text from a list in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-check-if-a-cell-contains-text-from-a-list-in-excel/
stats writer. "How can I check if a cell contains text from a list in Excel?." PSYCHOLOGICAL SCALES, 1 Jul. 2024, https://scales.arabpsychology.com/stats/how-can-i-check-if-a-cell-contains-text-from-a-list-in-excel/.
stats writer. "How can I check if a cell contains text from a list in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-check-if-a-cell-contains-text-from-a-list-in-excel/.
stats writer (2024) 'How can I check if a cell contains text from a list in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-check-if-a-cell-contains-text-from-a-list-in-excel/.
[1] stats writer, "How can I check if a cell contains text from a list in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, July, 2024.
stats writer. How can I check if a cell contains text from a list in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.