Excel: If cell contains formula examples

If cell contains any value, then

For starters, let's see how to find cells that contain anything at all: any text, number, or date. For this, we are going to use a simple IF formula that checks for non-blank cells.

For example, to return "Not blank" in column B if column A's cell in the same row contains any value, you enter the following formula in B2, and then double click the small green square in the lower-right corner to copy the formula down the column:

=IF(A2<>"", "Not blank", "")

The result will look similar to this:

If cell contains text, then

If you want to find only cells with text values ignoring numbers and dates, then use IF in combination with the ISTEXT function. Here's the generic formula to return some value in another cell if a target cell contains any text:

Supposing, you want to insert the word "yes" in column B if a cell in column A contains text. To have it done, put the following formula in B2:

=IF(ISTEXT(A2), "Yes", "")

If cell contains number, then

In a similar fashion, you can identify cells with numeric values (numbers and dates). For this, use the IF function together with ISNUMBER:

The following formula returns "yes" in column B if a corresponding cell in column A contains any number:

=IF(ISNUMBER(A2), "Yes", "")

If cell contains specific text

Finding cells containing certain text (or numbers or dates) is easy. You write a regular IF formula that checks whether a target cell contains the desired text, and type the text to return in the value_if_true argument.

For example, to find out if cell A2 contains "apples", use this formula:

=IF(A2="apples", "Yes", "")

If cell contains specific text string (partial match)

We have finished with trivial tasks and move on to more challenging and interesting ones :) To check if a cell contains specific a given character or substring as part of the cell content, you can use one of these formulas:

Formula 1

Working from the inside out, here is what the formula does:

• The SEARCH function searches for a text string, and if the string is found, returns the position of the first character, the #VALUE! error otherwise.
• The ISNUMBER function checks whether SEARCH succeeded or failed. If SEARCH has returned any number, ISNUMBER returns TRUE. If SEARCH results in an error, ISNUMBER returns FALSE.
• Finally, the IF function returns the specified value for cells that have TRUE in the logical test, an empty string ("") otherwise.

Formula 2

Here, the COUNTIF function finds out how many times a certain text appears in a cell. To count partial matches, you place the wildcard character (*) on both sides of the text. If the count is greater than zero, then IF returns the specified value, otherwise - a blank cell.

And now, let's see how this generic formula works in real-life worksheets.

If cell contains, then return value – multiple conditions

A single ‘if cell contains’ statement is easy, right? But what if you need to check multiple conditions in the same formula and return different results? That is, if a cell contains some text, then return something, if it contains another text, then return something else, and so on.

To evaluate multiple conditions in a cell, you can use nested "if cell contains" statements. This allows you to create a chain of checks, with each condition leading to a specific result. Here are two generic formulas to achieve this:

Formula 1

Use the COUNTIF function to count how many times a certain text appears in a cell. If the count is greater than zero, then return the corresponding value. Otherwise, check the next condition.

Formula 2

Use the SEARCH function to find the position of a certain text in a cell. If the position is a number, then return the corresponding value. Otherwise, check the next condition.

For example, to checks if cell A2 contains "apple", "banana" or "lemon" and return the corresponding name of the fruit in cell B3, you can use one of these formulas:

=IF(COUNTIF(A2, "*apple*"), "Apple", IF(COUNTIF(A2, "*Banana*"), "Banana", IF(COUNTIF(A2, "*lemon*"), "Lemon", "")))

=IF(ISNUMBER(SEARCH("apple", A2)), "Apple", IF(ISNUMBER(SEARCH("banana", A2)), "Banana", IF(ISNUMBER(SEARCH("lemon", A2)), "Lemon", "")))

To fit your specific needs, you can extend the chain of conditions as necessary to handle more cases.

If cell contains one of many text strings (OR logic)

To identify cells containing at least one of many things you are searching for, use one of the following formulas.

If cell contains several strings (AND logic)

In situations when you want to find cells containing all of the specified text strings, use the already familiar ISNUMBER SEARCH combination together with IF AND:

For example, you can find SKUs containing both "dress" and "blue" with this formula:

=IF(AND(ISNUMBER(SEARCH("dress",A2)),ISNUMBER(SEARCH("blue",A2))),"Valid ","")

Or, you can type the strings in separate cells and reference those cells in your formula:

=IF(AND(ISNUMBER(SEARCH($D$2,A2)),ISNUMBER(SEARCH($E$2,A2))),"Valid ","")

As an alternative solution, you can count the occurrences of each string and check if each count is greater than zero:

=IF(AND(COUNTIF(A2,"*dress*")>0,COUNTIF(A2,"*blue*")>0),"Valid","")

The result will be exactly like shown in the screenshot above.

How to return different results based on cell value

In case you want to compare each cell in the target column against another list of items and return a different value for each match, use one of the following approaches.

Practice workbook

Excel If Cell Contains - formula examples (.xlsx file)