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.
- “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.
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”
- Click on the cell where the search results would be displayed (C4).
- 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.
- Press Enter on your keyboard. “Yes” was displayed because Asbestos has a ‘be‘ in it
- 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.
- 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.
*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:
This formula would just display “Asbestos” if it has ‘be’ in it or it will display nothing if it doesn’t.