< Go Back

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:

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

Figure1_sample_spreadsheet

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

Figure2_click_on_the_cell

  1. 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

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

Figure4_press_ENTER

  1. 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

  1. 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.

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.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar