-(1).jpg)
Mobile Mastery: Transforming Work Habits with 8 iOS Productivity Techniques
🕤 07 Jan 2024 Read more
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.
Table of Contents
- Check Whether the cell contains the value in Google Sheet
- Check Whether a cell is a Number in Google Sheet
- Check Whether a cell is a Text in Google Sheet
- Check if the cell contains specific text in Google Sheet
- Check if the cell contains the exact substring in Google Sheet
- Check if the cell contains a substring in Google Sheet
- Check if the cell contains a partial text in Google Sheet
- Multiple string search and return true if any string exists in Google Sheet
- Multiple string search and return true if all string exists in Google Sheet
- How do nest multiple "IF" conditions with "cell contains" conditions in Google Sheets?
- How to use the "IF" condition with "cell contains" to a range of cells in Google Sheets?
- How to use the "If" function with "cell contains" across different sheets in Google Sheets?
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_expression, value_if_true, value_if_false)
For example:
=IF(A2<>"",A2,"#N/A")
Here we have a logical expression as A2 <> "" which means the 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 the correct order ie. First true value and then the Second false value.
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 these simple functions:
Example:
=IF(ISTEXT(A2),"Yes","No")
Check if the cell contains specific text in Google Sheet
Example:
=IF(EXACT(A2,"Docs"), "Yes", "No")
Note that search text is case insensitive. So, here we are searching "docs" and should have "docs" within the text and give a true result else false. So, for "Docs", "docs", and "DOCS" the result is true.
Check if the cell contains the exact substring in Google Sheet
Example:
=IF(REGEXMATCH(A2, "docs"), true, false)
Check if the cell contains a substring in Google Sheet
Example:
=IF(IFERROR(SEARCH("docs", A2) > 0, 0), true, false)
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_true, value_if_false)
Example:
=IF(COUNTIF(A2,"*docs*"),"Yes","No")
Alternatively
=IF(A2="*yourpartialtext*",value_if_true, value_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 Sheets with the 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 Sheets 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)
How do nest multiple "IF" conditions with "cell contains" conditions in Google Sheets?
Example:
=IF(REGEXMATCH(A1, "Tech"), IF(REGEXMATCH(B1, "Room"), "Yes", "No"), "No")
Here first IF condition checks whether cell A1 contains a string "Tech". If it is found then the second IF condition is evaluated and the second IF function checks if cell B1 contains the string "Room". If it has then it will return "Yes". If it does not contains then it will return "No".
If the first IF condition does not contain "Tech" then it will return "No" without executing the second IF condition.
How to use the "IF" condition with "cell contains" to a range of cells in Google Sheets?
For this, you can use below :
Example:
=IF(REGEXMATCH(A1:C10, "Ourtechroom"), "Yes", "No")
Here from range A1:C10 if there is a string that matches "Ourtechroom" then return "Yes" else return "No"
How to use the "If" function with "cell contains" across different sheets in Google Sheets?
For this, you can use IMPORTRANGE function. This function will import data from one sheet to another sheet. Here is an example:
=IF(A1=IMPORTRANGE("WorkSheet1", "A1"), "Yes", "No")
This formula will return "Yes" if the value in cell A1 in the current sheet is equal to the value in cell A1 in Worksheet 1.
.png)
.jpg)
