IF Contains in Google Sheets

Let’s say you have a list of tasks and want to find out if any task mentions “meeting.” You can use a formula to check this easily. In Google Sheets, you can use the IF Function to create a condition to test IF a cell contains a value or a text string that then returns a value, else returns another value.

Quick Formulas

• For a simple IF Contains: =IF(B2=”Meeting”, “Yes”, “No”)
• Using REGEXMATCH for Partial Text: =IF(REGEXMATCH(A2, “meeting”), “Yes”, “No”)
• Using Combo of IF, ISNUMBER, and SEARCH: =IF(ISNUMBER(SEARCH(“meeting”, A2)), “Yes”, “No”)
• Check-in an entire range of cell: =IF(COUNTIF(A1:A11, “*meeting*”), “Yes”, “No”)

Write a Simple Formula for IF Contains in Google Sheets

In the example below, we have s list of tasks and need to check if the cells in column B contain the text “Meeting”. Now, you need to write a formula with IF.

Syntax for IF – =IF(condition, value_if_true, value_if_false)

The formula used in cell C2 is =IF(B2=”Meeting”, “Yes”,”No”). This formula in cell C2 checks if B2 contains the value “Meeting”. If B2 is “Meeting,” the formula shows “Yes” in C2. If B2 is not “Meeting,” it shows “No”.

Check IF Contains with a Partial Text

Let’s say you want to check if a substring is there in a cell; I mean, you want to check if that text is there in the cells, and you have a long text in the cell. See the below example, where we need to check for the cells where you have the text “Meeting” within the cell.

You can use a combination of the IF and REGEXMATCH for this. The REGEXMATCH function in Google Sheets checks if a cell’s value matches a specified pattern. It returns TRUE if there is a match and FALSE if not. The formula will be:

=IF(REGEXMATCH(A2, "meeting"), "Yes", "No")

Here’s a simple explanation for this formula:

• REGEXMATCH(A2, “meeting”): This part of the formula checks if the word “meeting” is anywhere in the text of cell A2.
• IF Function: The IF function uses the result of REGEXMATCH. If “meeting” is found (REGEXMATCH returns TRUE), it shows “Yes”. If not (REGEXMATCH returns FALSE), it shows “No”.

We have used the formula in the entire columns to check which cells contain the text “Meeting”.

Note – REGEXMATCH is case-sensitive, differentiating between uppercase and lowercase letters. For example, REGEXMATCH(A2, “meeting”) will only match “meeting” and not “Meeting” or “MEETING.”

Use a Combination of IF, ISNUMBER, and SEARCH Test IF Contains

If you don’t want to consider the case of the text while checking if a cell contains that text, you can combine  IF   with ISNUMBER and SEARCH.

• ISNUMBER function checks if a value is a number. It returns TRUE if the value is a number and FALSE if not.
• SEARCH finds the position of a substring within a text string, ignoring the case. It returns the position number if found and an error if not found.

=IF(ISNUMBER(SEARCH("meeting", A2)), "Yes", "No")

The formula checks if the word “meeting” appears anywhere in cell A2. The SEARCH looks for “meeting” and returns a number if it finds it. ISNUMBER function then checks if SEARCH found “meeting” by seeing if the result is a number. If it is, ISNUMBER returns TRUE. The IF function then shows “Yes” if TRUE and “No” if FALSE.

So, if “meeting” is in the text of cell A2, B2 will show “Yes”. If not, B2 will show “No”.

IF Contains in the Entire Range

Let’s say you want to check for a text string in an entire range instead of a single cell. You need to use the COUNTIF with Wildcard Character (* Asterisk) for this. The formula is:

=IF(COUNTIF(A1:A11, "*meeting*"), "Yes", "No")

This formula checks if any cell in the range A1 to A11 contains the word “meeting”. First, the COUNTIF function counts how often “meeting” appears in that range. An asterisk (*) is a wildcard that means “any text before or after”.

If COUNTIF finds “meeting” at least once, it returns a number greater than zero. IF function then shows “Yes” if a match is found and “No” if not. So, this formula helps you quickly see if any tasks in the list involve a meeting.

Formula to Extract Data Based on IF Contains Result

Let’s say you want to extract all the tasks from the list. You can combine IF, SEARCH, FILTER, and ARRAYFORMULA.

=ARRAYFORMULA(FILTER(A2:A11, ISNUMBER(SEARCH("meeting", A2:A11))))

This formula helps you create a task list that includes the word “meeting” from your original list in column A. This formula is non-case sensitive as we have used SEARCH. The SEARCH function looks for the word “meeting” in each cell from A2 to A11, and ISNUMBER checks if “meeting” was found.

And then, the FILTER function selects only the tasks that match the condition. The ARRAYFORMULA then applies this formula to the entire range, working on all the rows at once.

Important Points

Here are some points you need to take while using any formula to check if the cell contains.

• Case Sensitivity –   When using formulas, consider case sensitivity to get the result.
• Incorrect Use of Wildcards – Make sure to use wildcard characters correctly; otherwise, it’s hard for the formula to look for the text in the cell properly.
• Quotes Around Text – When defining the text, enclose it in double quotation marks so that the formula will take it as a text.

Use a Custom Function with Google App Script

If you are comfortable using the App Script, add the code below to your Google Sheets file. To do this, go to Extensions > Apps Script. Then paste the code there and click on the save button to save.

=CHECK_TEXT("meeting",A2)

Here’s the link to the worksheet using this custom function.

/**
 * Custom function to check if a cell or range contains specific text.
 *
 * @param {string} textToFind The text to search for.
 * @param {Range} range The cell or range to search within.
 * @return {Array|string} "Yes" or "No" for each cell in the range.
 * @customfunction
 */
function CHECK_TEXT(textToFind, range) {
  if (typeof textToFind !== 'string') {
    return 'Invalid input';
  }
  
  var results = [];
  var values = range.map ? range : [[range]];
  
  for (var i = 0; i < values.length; i++) {
    var row = values[i];
    var resultRow = [];
    
    for (var j = 0; j < row.length; j++) {
      var cellValue = row[j];
      if (typeof cellValue !== 'string') {
        resultRow.push('Invalid input');
      } else if (cellValue.toLowerCase().indexOf(textToFind.toLowerCase()) !== -1) {
        resultRow.push('Yes');
      } else {
        resultRow.push('No');
      }
    }
    
    results.push(resultRow);
  }
  
  return results.length === 1 && results[0].length === 1 ? results[0][0] : results;
}