If Cell Contain Specific Text

Cell contains specific tex

This article will explain how to check if a cell contains text or a specific text string in Google Sheets or Microsoft Excel spreadsheets. In our daily work, we often look for specific text in worksheets containing large amounts of data or need to determine if a specific cell area contains text values.

We may also encounter the need to find out if a cell contains partial text or we need to find a specific text string in a cell by using VBA code.

Cell Contains Specific Text in Excel & Google Sheets

Below we will show how to check if a cell contains a specific text by using a formula in Excel or Google Sheets.

If you want to check if a given cell contains a specific text, then you can use the SEARCH function in combination with the ISNUMBER function to create a nested formula. You can refer to this general formula below.

=ISNUMBER(SEARCH(specific_text,text))

In the general formula above, specific_text is the specific text you want to find, and text is the text string or cell reference you want to find that contains the specific text.

If you found a specific text, then the formula will return TRUE, otherwise it will return FALSE.

If you need to find partial text from the cell, then the SEARCH function is very suitable; by default, the SEARCH function will automatically find partial text to match.

For the example in this article, you can use the following formula to find specific text.

=ISNUMBER(SEARCH(B4,A4))
Cell contains specific text

From the screenshot above, we can see that the formula supports partial text matching, where specific_text is “rest” and the text is “interest dividends“, and the result returned is TRUE, which means that partial text matching for a specific character is successful.

Let’s see how this formula works:

When the SEARCH function finds a specific text, it returns the position of that text in the searched text cell, and if not found, it returns #VALUE! . So, we can take advantage of this feature by simply using the ISNUMBER function to check if the SEARCH function returns a number, and if it is a number, it means that the specific text was found in the cell.

For the ISNUMBER function, it returns TRUE if the given argument is a number, otherwise it returns FALSE. therefore, when the SEARCH function can return the position of a specific character, then the ISNUMBER function returns TRUE. if the SEARCH function returns a #VALUE error, then the ISNUMBER function will return FALSE.

Cell Contains Specific Text with Case Sensitive in Excel & Google Sheets

You can use the SEARCH function to find specific text and partial text matches, but the function is not case-sensitive.

If you want to check for specific text in a case-sensitive manner in a text cell, then you cannot use the SEARCH function, and you need to replace the SEARCH function with the FIND function, using the following formula.

=ISNUMBER(FIND(B4,A4))
Cell contains specific text

As you can see from the above figure, specific_text “Rest” can no longer be successfully matched in text cell A4.

If Cell Contains Specific Text Then in Excel & Google Sheets

If you want to do something else when you find a cell contains a specific text, for example, we do not want to return TRUE or FALSE results, but rather return some more meaningful text or results.

You can build an IF nested formula by nesting the above formula into the IF function as follows.

=IF(ISNUMBER(SEARCH(B4,A4)),"Found", "No Found")
Cell contains specific text

Note: Because this formula uses the SEARCH formula, the result is case-insensitive.

Sum If Cell Contains Text in Google sheets & Excel

If a column of cells contains a specific text string, you want to add the corresponding cell value of another column, then you can use the SUMIFS function contains a specific text of the cells corresponding to the data of another column to sum. The general formula is as follows.

=SUMIFS(sum_range, criteria_range,specific_text)

If you want to find all the cells in the cell region A4:A12 contains the “expen” string, and the corresponding value of another column B4:B12 to sum, then you can use the following formula.

=SUMIFS(B4:B12,A4:A12, "*expen*")
Cell contains specific text

If you are using google sheets, then you can also accomplish this task by using the following formula, which is as follows.

=SUM(filter(B4:B12, regexmatch(A4:A12,"expen")))
Cell contains specific text

As you can see from the chart above, the formula consisting of the SUM function and the FILTER function returns the same result as the EXCEL formula.

Note: Excel does not have a special wildcard formula that uses the “*” meta-character to match any character. There is a special regular expression function regexmatch in google sheet.

Conditional Formatting If Cell Contains in Google Sheets & Excel

In Microsoft Excel, if you want to highlight a text cell containing the “expense” string, then you can refer to the following steps.

STEP 1: Select the cell range A4:A12, then in the HOME tab, click the Conditional Formatting button, and then click the New Rule submenu.

Cell contains specific text

STEP2: The New Formatting Rule window will pop up, click on “Use a formula to determine which cells to format“, then enter the following formula in the text box.

=SEARCH(“expense”,A4)
Cell contains specific text

STEP3: Click the Format button and select a color to fill the cells that can be matched successfully. Click OK.

Cell contains specific text

STEP 4: You will see that all cells that contain the expense string are automatically highlighted.

Cell contains specific text

In google sheets, if you want to highlight a text cell that contains an “expense” string, then you can refer to the following steps.

STEP1: Select the cell range that contains a specific text to be highlighted by conditional formatting

Cell contains specific text

STEP 2: Click Format menu, then click Conditional Formatting submenu, the “Conditional format rules” window will pop up on the right side of the page.

Cell contains specific text

STEP 3: Select Text Contains in the Format rules drop-down list and enter the specific text string “expense” in the text box

Cell contains specific text

STEP 4: Select a highlight color in Formatting style and click the Done button. 

Cell contains specific text

STEP 5: Let’s see the final result.

Cell contains specific text

Related Functions

  •  Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
  • Excel SEARCH function
    The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH  (find_text, within_text,[start_num])…
  • Excel Find function
    The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string.The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the FIND function is as below:= FIND  (find_text, within_text,[start_num])…
  • Excel SUMIFS Function
    The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…

Related Posts

Average Of Numbers With Multiple Criteria In Excel

Have you ever come across a task to calculate the average of the numbers with respect to multiple criteria? Are you tired of doing this cumbersome task manually? Are you willing to do this task smartly in just a matter ...

How to Sum if Equal to Many Items or A Range in Excel

If we want to sum numbers from a range with criteria, we often select SUMIF of SUMIFS functions as the first choice to create a formula. The criteria can be a number or an array or a collection of some ...

How to Sum if Equal to X or Y in Excel

In daily work, if we want to sum numbers from a range, and only sum the numbers which being equal to X or Y in the range, we can create a formula with Excel build-in functions to get the result. ...

How to Sum Last N Days in Excel

In our daily life, we may want to sum amounts or sales for a specific period, for example in last N days. Sum numbers in Excel is easy to run, we can apply SUM function. But if we want to ...

How to Sum if Contains an Asterisk

To add numbers together we need to apply SUM function. And if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. If ...

Sidebar