In Google Sheets, if you want to check a value from a cell, whether it contains a value or not, you can use a formula by combining IF, ISNUMBER, and SEARCH functions.
In the above example, we have a few values in Column A, and from each cell, you need to check whether the value “Sheets” is there or not. And for this, we have used the below formula:
=IF(ISNUMBER(SEARCH("Sheets",A4)),"Yes","No")As you can see, this formula is a combination of three functions, and to understand how this formula works, you need to split it into three parts.
SEARCH
With the search function, you can check if a value (string or a number) is there in the cell not. If the value is there, it returns the position of the first character of the value in the result.
And if that value is not, there returns the #VALUE! Error in the result.
Note: Instead of SEARCH, you can also use the FIND function. The difference between both functions is that FIND is case-sensitive, and SEARCH is not. And if you want to search for value case-sensitively, you can use FIND.
ISNUMBER
ISNUMBER takes the value return by the SEARCH. If the result from the SEARCH is a number (which means the value is there in the cell), ISNUMBER returns TRUE, or else, FALSE.
Above, you can see in cells A1 and A4 that we have TRUE because the value “Sheets” is there in these cells.
IF
Once ISNUMBER returns TRUE or FALSE, IF takes those value and return “Yes” for TRUE and “No” for false.
