Are you struggling to find specific text in your Google Sheets? Look no further! In this tutorial, we’ll show you how to use the IF CONTAINS function in Google Sheets to search for cells that contain a specific string of characters. Let’s dive in!
Syntax of IF CONTAINS Google Sheets
There are two ways to write the IF CONTAINS formula in Google Sheets.
Syntax IF+REGEXMATCH
=IF(REGEXMATCH(range, string), value_if_true, value_if_false)- range is the cell reference where we want to perform the search.
- string is the string of characters (which can also be a number) that we want to search for.
- value_if_true is the value to return if the string is found.
- value_if_false is the value to return if the string is not found.
Syntax IF+SEARCH
=IFERROR(IF(SEARCH(string, range), value_if_true), value_if_false)- string is the string of characters (which can also be a number) that we want to search for.
- range is the cell reference where we want to perform the search.
- value_if_true is the value to return if the string is found (enclosed in double quotation marks).
- value_if_false is the value to return if the string is not found (enclosed in double quotation marks).
We wrap the statement in IFERROR because the SEARCH function returns an error if the string is not found in the selected range.
Examples of IF CONTAINS Google Sheets
Let’s walk through some examples to see how the IF CONTAINS function works in action.
Checking if a Cell Contains Specific Text using IF + REGEXMATCH
In this example, we will use the IF function in combination with the REGEXMATCH function to compile a list of people who obtained a “Matric” certificate in their final exams.
Step 1: Identify the string and where to perform the search
We will search for the string “Matric” in the column titled Graduation. The function will be placed in a column titled Message.
Step 2: Add the function =IF(REGEXMATCH(range, string), value_if_true, value_if_false)
Let’s define the following values using row 2 as an example:
- String: “Matr”
- Range: E2
- Value_if_true: “Congratulations”
- Value_if_false: “None”
The function will become:
=if(regexmatch(E2, "Matr"),"Congratulations","None")We shorten “Matric” to “Matr” to demonstrate that our function works for detecting whether the range contains the given text or string.
Step 3: Check the output
Note that REGEXMATCH is case-sensitive, which is why row 4 is not detected as it contains “matric” instead of “Matric”. For a case-insensitive solution, refer to the next example.
Checking if a Cell Contains Specific Text using IF+SEARCH
Let’s do the same example as before, but this time, we will use the SEARCH function.
Step 1: Identify the string and where to perform the search
We will search for the string “Matric” in the column titled Graduation. The function will be placed in a column titled Message.
Step 2: Add the function =IFERROR(IF(SEARCH(string, range), value_if_true), value_if_false)
Let’s define the following values using row 2 as an example:
- String: “Matr”
- Range: E2
- Value_if_true: “Congratulations”
- Value_if_false: “None”
The function will become:
=iferror(if(search("Matr",E2),"Congratulations"),"None")We shorten “Matric” to “Matr” to demonstrate that our function works for detecting whether the range contains the given text or string.
Step 3: Check the output
The difference this time is that SEARCH is case-insensitive, so it detects “matric” as identical to “Matric,” marking it as well.
Conclusion
While there is no explicit IF CONTAINS function in Google Sheets, we can combine IF with the REGEXMATCH and SEARCH functions to achieve the same results. The IF+REGEXMATCH solution is best for case-sensitive searches, while the IF+SEARCH solution is ideal for case-insensitive searches.
So, go ahead and give these techniques a try and supercharge your Google Sheets with the IF CONTAINS function!
Doping for Your Spreadsheets with Lido
Related IF Functions
- IF: Used to check specific conditions in a dataset.
- SUMIF: Add numbers if they meet certain conditions.
- SUMIFS: Add data from cells that meet multiple criteria.
- COUNTIF: Count data if it meets specific criteria.
- COUNTIFS: Count data that meets two or more criteria.
- COUNTIF Not Null: Count cells if they contain data.
- IFS: A more elegant way to evaluate data against multiple criteria.
- IFERROR: Return values when an error occurs in a formula.
- IF THEN: Write statements that use the logic of IF X Then Y.
- IF AND: Combines the functionality of the IF and AND functions.
- IF ELSE: Set conditions that give an output based on the fulfillment or non-fulfillment of a given condition.
- Multiple IF Statements: How to chain multiple statements together.
- IF OR: Combines the functionality of the IF and OR functions.
- AVERAGE IF: Calculate an average for numbers in a data range if they meet the provided criteria.
Use our software on dental appointment reminders to easily set up customized reminders from your spreadsheet with just a few clicks.
If you’re interested in learning how to create a Google Sheets contact list template, we also recommend checking out our detailed guide.
