Excel formula: If cell contains

What is the “if the cell contains” formula?

The formula was developed using the IF function together with the SEARCH and ISNUMBER functions and it helps make it easy to take an action, just as long as a cell contains a specific term. It’s simple; if a cell contains a specific term (which will be entered into the formula), the If the cell contains formula will take a specific action on the cell while leaving all other cells within the specified remain unchanged. When you find the text you’re looking for you can copy it to another cell, or simply display a message, or even perform some other calculation.

Formula

=IF(ISNUMBERSEARCH"abc",cell_num,[value_if_TRUE],[value_if_FALSE])

Explanation

“abc” – this is the text used as the search keyword.
cell_num – the cell reference to the cell being searched.
“value_if_TRUE” – what to display if the search proved positive i.e. Text was found in that cell.
“value_if_FALSE” – what to display if the text was not found.

Example

How to use the “if cell contains” formula

In the spreadsheet in this example, we have a list of words, and we want to check if they contain “be“.

The following steps should be taken:

Open your working spreadsheet. Let’s start with cell B4 containing the word “Asbestos”

Figure1_sample_spreadsheet

Click on the cell where the search results would be displayed (C4).

Figure2_click_on_the_cell

Insert the formula; =IF(ISNUMBER(SEARCH("be",B4)),"Yes","No") into the cell.

“Yes” is displayed if the word “Asbestos” contains the text “be” within it and “No”, if it doesn’t.

Figure3_insert_formula

Press Enter on your keyboard. “Yes” was displayed because Asbestos has a ‘be‘ in it

Figure4_press_ENTER

Next up, we search in cell B5 containing ‘Johnny’. Insert the formula =IF(ISNUMBER(SEARCH("be",B5)),"Yes","No") and press Enter. The result should be ‘No’ and that’s because ‘Johnny’ has no ‘be’ in it.

Figure5_2nd_example

Lastly, let us check the word “Beach”. In cell C6, insert the formula =IF(ISNUMBER(SEARCH(“be”,B6)),”Yes”,”No”) and Press Enter. The result is “Yes”. “Beach” has a “be” in it. The formula is not case sensitive.