Table of Contents
In Excel, you can check if a cell contains a partial text by using the SEARCH or FIND function. The SEARCH function takes in two arguments, the text string you are searching for and the text string you are searching in, while the FIND function takes in one argument, the text string you are searching for. Both the SEARCH and FIND functions will return the position of the text string you are searching for in the text string you are searching in, and if the position is greater than 0, it means that the text string you are searching for exists in the text string you are searching in.
You can use the following formula in Excel to determine if a cell contains specific partial text:
=IF(COUNTIF(A1,"*abc*"),"Yes","No")
In this example, if cell A1 contains the string “abc” in any part of the cell 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 Partial Text 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 partial text “guar”:
=IF(COUNTIF(A2,"*guar*"),"Yes","No")
We can type this formula into cell C2 and then copy and paste it down to the remaining cells in column C:
Any rows with the partial text “guar” in the Team column all receive a Yes in the new column while all other rows receive a No.
Also note that we could return values other than “Yes” and “No.”
For example, we could use the following formula to return “Guard” or “Not Guard” instead:
=IF(COUNTIF(A2,"*guar*"),"Guard","Not Guard")
The following screenshot shows how to use this formula in practice:
