How to find Cells that DO NOT contain a given text in Excel  

A few weeks ago, one of my friends asked me how to find cells that do not contain a given text and return “TRUE” for such cells. 

I started thinking about this. 

I worked on excel and figured out the following two ways.

One way is to use COUNTIF and wildcard characters. 

The other way is to use the SEARCH function.

I will be explaining the first method in more detail below.

Here is a snapshot of the file that I have created to explain the concept.

The cells to be searched are from A4 to A12. The formula will return “TRUE” or “FALSE” in the cells B4 to B12. 

We will be trying to find out all the cells which do not contain the word "excel" including its variations. 

Note that any cell which contains variations of the word “excel” like MSExcel or “excellent” will also be considered to contain the word ”excel” within it. 

Approach 1: Using COUNTIF & Wildcards 

In the first approach we use an IF statement and COUNTIF with wildcards.  

Using the COUNTIF function, we will find all the cells that contain the word excel or its variants by using wildcards.

So this will give us all the cells containing Excel.  Then we will use the NOT function to change the output to negative and use any function to return the true or false accordingly. 

Here is the formula that we will use. This is the formula used in cell B4.

=IF(NOT(COUNTIF(A4,"*excel*")),TRUE,FALSE) 

Let us understand this formula in more detail.

As you may be aware, a COUNTIF function returns the number of times a particular text value is found in the cell. 

If the word “Excel” or its variants (like excellent) are FOUND in that cell, it will return 1 for every such cell found. Since we are checking it in only one cell (Cell A4 in this example), the maximum value that it can return is 1.  

If “Excel” is not found, then this function will return the value 0. 

Do note  that we have used the wildcard * symbol  before and after the word “excel”.  

This ensures that even if the word “excel” comes in between or at the end of the text, we will still be able to find it. Otherwise, COUNTIF will return the value of 1 only if the cell contains only the word “excel” (like in cell A7).

Thus, we have found all the cells that contain “excel” or its variants. 

NOT Function to Invert the Result

Since we want to return the value of TRUE if the cell does not contain the word “excel”, we invert this by using the NOT function.

Then we use the IF Function to return true if the word is not found and return FALSE if the word is found in the cell. 

Thus the IF function returns “TRUE” for all the cells that "do not contain" the word “excel”.

There is another approach to achieve the same using the SEARCH function. I will cover the same in an upcoming blog.

(Meanwhile, If you want to know more about the SEARCH Function, or learn the key differences between SEARCH and FIND function, check this post).

Hope you found this post interesting. If you have found other ways of achieving the same, do share the same. 

Let me know your comments, questions or suggestions in the comments.

PS: Are you trying to learn MS Excel in a fun way? Or, you think you know MS Excel well? 

Try this puzzle now and test yourself.

https://www.mlbrains.com/learning/ 

 It's free and a lot of fun!