Excel if cell contains specific text
In this tutorial, we will learn about how to use different “Excel if contains” formulas to do the following: get a value from another column based on a cell that has a certain value, look for a value that matches part of a cell, and check multiple conditions with OR or AND logic. We see examples of If cell contains any value, IF cell contains specific text
If cell contains any value
To begin with, let us see how to locate cells comprising everything; text, number, or date. This will require us to use an easy Excel IF formula which tests for cells that are not empty.
IF(cell<>””, value_which_return, “”)
For Example:
This is not a theoretical but a practical example of a list of tasks. You have a list of tasks in column A, and you need to write a formula that will put ‘completed’ or ‘not completed’ in column B depending on whether each task is done. Here’s an example of the formula you could use:
=IF(A2<>” “, “Completed”, “Not Completed”)
If you enter a task in cell A2, the corresponding B2 cell will show “Completed.”
When there is no task in cell A2, which is empty, B2 will display ‘Not Completed.’
Now you can copy this formula down the column and it will update automatically depending on what is inputted into the adjacent cells in column A. So you can tell which tasks are completed in column B very easily.
IF cell contains specific text
Without a doubt! For instance, you might want to use a formula that verifies if there is any cell with the word “Important” in column A. If true, display “High Priority” in column B; if not, fill in “Normal Priority”. Here’s how you set up the formula:
You would use the following formula in cell B2:
=IF(ISNUMBER(SEARCH(“Important”, A2)), “High Priority”, “Normal Priority”)
This function checks whether the contents of cell A2 contain the word “Important” or not.
The ISNUMBER function determines whether SEARCH returns a number, which means that it contains text.
If it does, then our formula gives us this “High Priority”; otherwise, it presents us with this “Normal Priority”.
Once you have put this formula into B2, you can copy it down the column to apply the logic to other cells in column A. This will make it possible for the result to constantly update itself contingent upon whether or not the given text is present in each cell of column A.
If a cell does not contain specific text
To find out whether a cell contains specific text that is not present in any cell within column A and shows a certain value in column B based on the above condition, use the following formula:
If you want to display “Not Found” in column B if the cell within column A lacks the word, “Important,” you can use this formula in cell B2.
Regarding this formula: Let’s go over that formula;
SEARCH(“Important”, A2) checks whether the content of cell A2 includes the text “important”.
ISNUMBER checks whether what SEARCH returns is a number (meaning the text was found).
It returns an empty string (“”) if it finds the text, meaning “not found” and “Not Found” otherwise.
Once you enter this formula into B2 simply copy it down the column to extend this logic to all other cells in column A. The outcome will update automatically as per the presence or absence of certain text in each of the cells in column A.
