**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**;

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

*“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 As**be**stos has a ‘**be**‘ in it

*Figure4_press_ENTER*

- Next up, we search in cell
**B5**containing ‘Johnny’.**Insert**the**formula**and press`=IF(ISNUMBER(SEARCH("be",B5)),"Yes","No"`

)**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”. “**Be**ach” has a “be” in it. The formula is not case sensitive.

*Figure6_3rd_example_SEARCH_is_not_case_sensitive*

**Notes**

*The formula is not case sensitive.

*To simply copy the item you’re searching and paste it as a result instead of ‘Yes’ or ‘No’, use the **formula:**

** =IF(ISNUMBER(SEARCH("be",B4)),B4,"")**

** **This formula would just display “Asbestos” if it has ‘be’ in it or it will display nothing if it doesn’t.

## Leave a Comment