How To Use “If Cell Contains” Formulas In Excel [Ultimate Guide]

"If Cell Contains" formula is one of the most useful formulas in Excel. Here's your comprehensive guide on how to use "If Cell Contains" in Excel. Read on.

While Microsoft Excel is already one of the most popular and recognizable computer programs today, you may not know that it’s also been around since the 80s. And in that time, many operating systems and software have come out – like the Windows 10 Pro , and Excel has seen many changes, but its core function remained the same.

Excel is used to make convenient spreadsheets. That way, data is compiled in a neat and proper manner that’s easy for different people to view, interpret, and understand. Additionally, you can put specific formulas and functions into the cells so they do the tasks for you.

And one of the most useful formulas for Excel is the “If Cell Contains” formula. But sadly, despite how useful this formula is, not a lot of people know how to use it. At the end of the day, it’s much more difficult than deleting a page on Microsoft Word  or setting up a new spreadsheet.

If you’re part of this club, don’t worry, you aren’t alone. In fact, there may be people out there who have been using Excel for years and still don’t know how to use the “If Cell Contains” Formula to find specific data in their sheets.

With all that said, learning about this specific formula isn’t hard. And we’re here to make it even easier for you.

In this guide, we’ll be taking a close look at the “If Cell Contains” formula in Excel. We will explain how it works, when you should use it, and how to use it to achieve your goals when making spreadsheets.

Table of ContentsShow

What Is the “If Cell Contains” Formula?

Question

The reason the If Contains formula in Excel is so useful is because it’s a combination of three different functions. This formula combines the IF function with the SEARCH and ISNUMBER functions.

The generic formula for this function can look something like this: IF(ISNUMBER(SEARCH(“abc”,B5)),B5,””). You can use these functions to test your cells if they contain specific values, texts, or even strings of text.

An example of this formula in use would be searching for the text “example” in certain cells. You can program the sheet to search for the text in the cells and produce a Yes or No in adjacent cells depending on whether or not they found the text.

This formula is designed to get rid of the limitations of the IF formula. The IF formula is very useful, but it is fairly limited. To start, this function doesn’t support Excel wildcards like the “*’ and “?” symbols. That is why the IF formula is largely paired with the ISNUMBER and SEARCH functions.

While the “If Cell Contains” formula is very useful, it can be hard to understand, especially since there are a few variations in Excel. If you have the latest version of Microsoft Excel on your computer, these are the following variations to the formula;

  • If cell contains any value
  • If cell contains text
  • If cell contains specific text
  • If cell contains number
  • If cell contains one of many text strings
  • If cell contains several strings
  • If cell contains certain text string

All of these variations produce different results but follow the same general concept. In the rest of this article, we’ll be taking a look at these different variations in use and how you can use them in your spreadsheets.

Excel professionals always say that the “If Cell Contains” formula is one of the most advanced Excel formulas you must know.

How To Use “If Cell Contains” Formulas

Excel

To understand how to use the “If Cell Contains” formula in your spreadsheets, you have to understand its variations. After all, each of these variations is designed to handle specific tasks and queries.

In this section, we’ll be taking a close look at these variations and how you may want to use them in your spreadsheet.

If Cell Contains Value, Then Return Value

This is the most basic version of the formula. As the name suggests, this formula is used to check if a cell has any value at all. Then, if the cell contains a value, that value will be returned. However, if the cell is blank and doesn’t contain a value, then the formula will react accordingly.

To use this formula yourself, you have to;

  • Select the output cell (where you want the value returned)
  • Input the following formula; a: =IF(cell<>””, value_to_return, “”)
  • Place the appropriate variables in the quotation marks
  • For example, if you want to check the value of cell A4 and have the return value be “Yes”, then the formula will look like =IF(A4<>””, “Yes”, “”)
  • So, if the cell has value, it will return “Yes” and if it doesn’t have value, then the cell will remain blank

If Cell Contains Text, Then Return Value to Another Cell

You can interchange the “text” in this formula with “number” and achieve the same results. This formula will focus on just text or numbers, depending on what you input and ignore all other data types. To use this formula, here are the steps;

  • Choose the output cell and enter IF(ISTEXT(cell), value_to_return, “”)
  • For example, if you want to check cell A3 for a text and have the return value be “Yes”, the formula will be as follows; IF(ISTEXT(A3), value_to_return, “Yes”)
  • If you’re searching for numbers, replace ISTEXT with ISNUMBER
  • If a number or text appears in the cell you’re viewing, then the “Yes” value will be reflected
  • If there is no number or text, then the output cell will be blank

If Cell Contains Specific Text, Then Return Value

Excel on PC

The previous formulas are used to determine if certain cells have any text at all. However, if you want to search for specific text and disregard other text, this is the formula to use. Here’s an application of that specific formula;

  • Select the output cell and input =IF(cell=”text”, value_to_return, “”)
  • Again, replace cell with your same number and input the value to return within the quotation marks
  • For example, if you’re looking for the text “charm” in cell A4, the formula will be =IF(A4=”charm”, value_to_return, “Yes”)
  • You can make the search case sensitive by adjusting the formula as follows:  =IF(EXACT(A4,”CHARM”), “value_to_return”, “Yes”)
  • If you use the case sensitive variation example, then Excel will disregard cells with “charm” in them and only count ones that have “CHARM” in them

You can also reverse this and set a value to pop up in the output cell if the cell doesn’t contain a specific text. To do this using the above example, all you have to do is change the “value to return” to “No”.

If Cell Contains One of Many Text Strings, then Return Value

If you are searching for many different words and texts and want to find cells that have at least one of these words in them, this is the formula you need. To use this function you have to;

  • Select the output cell and input =IF(OR(ISNUMBER(SEARCH(“string1”, cell)), ISNUMBER(SEARCH(“string2”, cell))), value_to_return, “”)
  • For example, let’s check if cell A1 contains the words “cat” and “dog”
  • The formula will then look like; =IF(OR(ISNUMBER(SEARCH(“cat”, A1)), ISNUMBER(SEARCH(“dog”, A1))), value_to_return, “Yes”)
  • With this formula, if the cells contain the text “cat” or “dog” the output cell will have “Yes” in it

If Cell Contains More Than One of Many Text Strings, then Return Value

Similar to the previous variation, this one works by searching for specific text strings in a certain cell. With this specific variation, the value in the output cell changes if it finds a certain cell has one or more of the text strings you’re searching for. To use this formula, follow these steps;

  • Input =IF(AND(ISNUMBER(SEARCH(“string1”,cell)), ISNUMBER(SEARCH(“string2″,cell))), value_to_return,””) in the output cell
  • For example, let’s check if A5 contains “white” and “cat” in it, which means the formula will look like this:  =IF(AND(ISNUMBER(SEARCH(“white”,A5)), ISNUMBER(SEARCH(“cat”,A5))), value_to_return,”Yes”)
  • With this formula, if cell A5 contains the words “white” and “cat”, then the return value in the output cell will be “Yes”
  • If it contains only one of these words or none of them, then the output cell will remain blank

When Should I Use “If Cell Contains” Formulas?

Excel Formula

The best way to use the “If Cell Contains” formula is as a search tool in your Spreadsheet. For example, let’s say you have a spreadsheet and you’re only looking for cells that contain text or numbers in them. You can use the formula to find out which specific cells have value in them and have the value returned in the output cell.

Additionally, you can search for specific words, numbers, and even text strings. So, while it may be a largely underutilized Excel formula, it remains one of the most useful ones available. If you want to bring your Excel skills to the next level, then you might want to consider using this formula in your work.

But before you start testing out this formula, you should learn how to recover a document[3]  just in case you mess up the spreadsheet or it accidentally closes with all of your progress.

Conclusion

For first-timers, Excel can be confusing. But as any seasoned Excel user will tell you, once you get over the learning curve, then it becomes incredibly easy. And while this applies to the Excel program as a whole, you can also use this thinking when learning different formulas.

The “If Cell Contains” formula is highly useful, especially when dealing with large spreadsheets. However, you have to make sure to use them properly to get the most out of the program.

And now that we’ve laid everything out for you with examples, it might be time to head over to your computer and try out these formulas for yourself.

Related Reads

Which Is Bigger MB or KB
Which Is Bigger: MB or KB?
Daniel Taylor

Daniel is a senior software engineer who loves following the evolution of technology. He began writing in 2015 and his published posts have been read more than 10 million times.