How to check if Google Sheets cells contain 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.

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.

Check if a range contains substrings 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 range A11 (starting from it’s beginning), then leave the C11 cell empty.
  • Else write a “+” sign.

Search for numbers in a Sheets range

Related: In the same fashion you can check also whether a specific number if contained in your Sheets cell or column range. This will return the value yes if the number 99 is found in column A:

=IF(ISERROR(FIND(99, A:A,1)),,"yes")

Check multiple conditions on Sheets cells and ranges

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 :-).