Excel: How to Check if Cell Contains Partial Text


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:

Excel check if cell contains partial text

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:

Any rows with the partial text “guar” in the Team column all receive a value of Guard in the new column while all other rows receive a value of Not Guard.

Additional Resources

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

How to Count Frequency of Text in Excel
How to Check if Cell Contains Text from List in Excel
How to Calculate Average If Cell Contains Text in Excel

Leave a Reply

Your email address will not be published.