How to check if a cell in Google Sheets contains specific text or values?

by

Here’s a question from Randy:

“I have a pretty long Google Sheets worksheet and need to be able to search for specific words in one of the columns. Seems that Sheets has somewhat different functions than Excel to find text in cells. Can you provide a simple example of how would you go about solving this issue?”

Check for specific text in Google Sheets columns

Thanks for the question. Similarly to Excel, in Sheets you are able to use the IF function to check if your cell content meets some logical conditions. In this case we will use it in conjunction with the REGEXMATCH and FIND functions that allows to evaluate if a specific text or part of it is written in your Sheets cell.

Example #1: Evaluate if a cell contains specific text

In the following spreadsheet we would like to determine for each course, whether it is a beginner or advanced course. The logic is determined by sub strings in the course name. 

=if (REGEXMATCH(A3, "fundamentals"), "+",)

The formula is interpreted by Sheets in the following way:

  • If the word fundamentals exists in cell A3 (that is evaluated using the REGEXMATCH function), then a “+” sign is written in cell C3.
  • If the word or pattern is not found, then the cell is kept empty.

Example # 2 using the FIND function to check conditions in Sheets

We can use the FIND function alongside with the ISERROR function to accomplish a similar outcome. Here’s the screenshot and formula:

=IF(ISERROR(FIND("fundamentals", A11,1)),,"+")

The syntax is interpreted in a bit different way:

  • If the word fundamentals is not found in the string written in cell A11 (starting from it’s beginning), then leave the C11 cell empty.
  • Else write a “+” sign.

Example # 3 check a complex condition on a Sheets column

In this next example, we will use the REGEXMATCH with a slightly bit more complex condition.

This formula checks for text that includes the words fundamentals or the word professionals:

=if(REGEXMATCH(A3, "fundamentals|professionals"), "+",)

And this one will search for the word advanced and the word professionals:

=if(REGEXMATCH(A3, "fundamentals&professionals"), "+",)

Hope it helps, kindly let us know in case of further questions :-).