How to use if a cell contains in google sheets ?


👤 Diwas Poudel    🕒 Oct 16 2021    📁 Fix    📜 0 comment

Google sheet has many useful inbuilt in rich functions and formula that helps to easily manipulate data. But there is no specific function like IF CONTAINS to do specific action if a certain condition is met like if found specific string, or substring in a string. This task in Google Sheets is commonly used to check whether a cell contains a value of interest and does act according to that. We can achieve this functionality by combining other functions and conditional statements.
In this article, We will demonstrate various methods to use if contains in Google Sheets and how to assign value based on if statement is true or false.

Check Whether the cell contains the value in Google Sheet

We can use the below formula to check whether the cell contains the value or not. If a cell contains value then return true else false.
 
IF(logical_expressionvalue_if_truevalue_if_false)
For example: 
=IF(A2<>"",A2,"#N/A")
Here we have a logical expression as A2 <> "" which means Value in A2 Cell is not Empty. So, this query means if the value in A2 is not Empty then Display A2 otherwise "#N/A".Note that value_if_true and value_if_false should be in correct order ie. First true value and then Second false value.
 
empty-check-if-contains
 

Check Whether a cell is a Number in Google Sheet

ISNUMBER(CellNumber) checks whether the value in that Cell Number is a number and if it is a number then will display "Yes" else display "No".We can check whether the content of the cell is a number or not with this simple functions:
=IF(ISNUMBER(A2),"Yes","No")

Check Whether a cell is a Text in Google Sheet

ISTEXT(CellNumber) checks whether the value in that Cell Number is text and if it is text then will display "Yes" else display "No".We can check whether the content of the cell is a number or not with this simple functions:
 
Example:
=IF(ISTEXT(A2),"Yes","No")
 
check-whether-text-or-number

Check if the cell contains specific text in Google Sheet

Example:
=IF(EXACT(A2,"Docs"), "Yes", "No")
 
find-exact-in-googlesheet
Note that search text is case insensitive. So, here we are searching "docs" and should have "docs" within the text and give true result else false.So, for "Docs","docs","DOCS" result true.

Check if the cell contains the exact substring in Google Sheet

Example:

  =IF(REGEXMATCH(A2, "docs"), true, false)

find-exact-substring-googlesheet

Check if the cell contains a substring in Google Sheet

Example:

=IF(IFERROR(SEARCH("docs", A2) > 0, 0), true, false)

find-substring-in-string-google-sheet

or

=IF(A2="docs", true, false)

Check if the cell contains a partial text in Google Sheet

We can achieve this with the help of wildcards(*).

Syntax:

=IF(COUNTIF(Cell,"*YourPartialText*"),value_if_truevalue_if_false)

Example:

=IF(COUNTIF(A2,"*docs*"),"Yes","No")

find-partial-text-google-sheet

Alternatively

=IF(A2="*yourpartialtext*",value_if_truevalue_if_false)

example:

=IF(A2="*docs*","yes", "no")

Multiple string search and return true if any string exists in Google Sheet

We can do it easily in google sheet with following formula.

=IF(OR(ISNUMBER(SEARCH("search string 1", cellNumber)), ISNUMBER(SEARCH("search string 2", cellNumber))), value_to_return, "")

Here, we are using only two search strings but we can keep adding like  ISNUMBER(SEARCH("search string 3", cellNumber)) and soon

Example:

=IF(OR(ISNUMBER(SEARCH("docs",A2)),ISNUMBER(SEARCH("word",A2))),"true","false")

Multiple string search and return true if all string exists in Google Sheet

We can do it easily in google sheet with the following formula.

=IF(AND(ISNUMBER(SEARCH("search string 1", cellNumber)), ISNUMBER(SEARCH("search string 2", cellNumber))), value_to_return, "")

Here, we are using only two search strings but we can keep adding like  ISNUMBER(SEARCH("search string 3", cellNumber)) and soon

Example:

=IF(AND(ISNUMBER(SEARCH("docs", cellNumber)), ISNUMBER(SEARCH("word", cellNumber))), true, false)