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 creates useful spreadsheets to organize data in an orderly fashion that’s straightforward for all to see, analyze, and comprehend. Also, you may input particular equations and actions for the cells to carry out on your behalf.
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?
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
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
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 does not 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
Like the previous variation, this one operates by searching for particular text strings in a specific cell. With this particular variation, the value in the output cell alters if it discovers a certain cell has one or more of the text strings you are seeking. To utilize this formula, proceed with 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?
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.
Moreover, you can explore for particular words, figures, and even sequences of words. Consequently, even though it may not be widely used in Excel, it is still among the most practical functions accessible. If you aim to enhance your Excel abilities, you might want to contemplate utilizing this formula in your tasks.
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 extremely handy, particularly when dealing with expansive spreadsheets. However, you have to ensure using them appropriately to maximize the program’s potential.
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.
