Introduction
When working with a large database, we may need to find a specific text. Excel includes numerous tools that can help us achieve this quickly. The blog provides some formulas to check if a cell has a specified text and return TRUE or FALSE and explanations of the arguments and how the formulas function.
Check if Cells Contain Specific Text
In Excel, conditions may require determining whether or not a cell contains a specific text. We'll understand how to check if a cell has specified text today in the following 4 methods.
First and foremost, let's learn about the dataset that will serve as the foundation for our examples.
Using COUNTIF Function
We will use a formula that will be based on the COUNTIF function to see if a value or text exists in a set of data.
- Consider the case where the 'Sentence' column contains a variety of distinct types of texts. We are given specific definite texts in a column called 'Specific Text', which we must find from the 'Sentence' column and show our results in the 'Output' column. This section will use the COUNTIF formula to complete this task.
- Now in cell ‘C2’, we will apply the COUNTIF function. The formula for the COUNTIF function is,
=COUNTIF(range, value)>0 |
We will now insert the values into the function and our final form of the function is,
=COUNTIF(A2:A6,’’*”&B2&”*”)>0 |
Where,
- Range is A2:A6
- "*"&B2&"*" is the criterion. The asterisk (*) Is used as a Wildcard for one or more characters in this case. The asterisk has been concatenated before and after the cell reference B2; therefore, it will now be counted as a substring. As a result, if the value appears anywhere in the range, it will be counted.
- The result is TRUE if the value is found; otherwise, the result is FALSE.
Hover your mouse cursor over the formula cell's bottom right corner, and when the Fill handle icon (+) appears, double-click it to apply the same formula to the rest of the cells.
If we add some specific texts to the Sentence column that do not already exist, the formula will return FALSE.
Using ISNUMBER Function
We can find a certain text from a range of cells using the formula based on the (ISNUMBER and FIND) and (ISNUMBER and SEARCH).
CASE-SENSITIVE
Generic Formula:
=ISNUMBER(FIND(substring, text)) |
Arguments:
Substring: The substring is the text we wish to look for in the cell. Text: The cell or text string that we wish to see if it contains the specific text (the argument substring). |
Return Value:
The result of this formula is a logical value. The formula returns TRUE or FALSE depending on whether the cell contains the substring.
Working:
If you want to see if cell A6 has the text in cell B6, apply the formula below.
=ISNUMBER(FIND(B6, A6)) |
CASE-INSENSITIVE
Generic Formula:
=ISNUMBER(SEARCH(substring, text)) |
Arguments:
Substring: The substring is the text we wish to look for in the cell. Text: The cell or text string that we wish to see if it contains the specific text (the argument substring). |
Return Value:
The result of this formula is a logical value. The formula returns TRUE or FALSE depending on whether the cell contains the substring.
Working:
If you want to see if cell A3 has the text in cell B3, apply the formula below.
=ISNUMBER(SEARCH(B3, A3)) |
Using IF Function
Whenever we need to find specific texts from a range of cells, we can use the IF function to accomplish it quickly. We may make our job easier by nesting other functions within the IF function.
IF with COUNTIF Function
We will apply the IF formula with the COUNTIF formula to a cell where we want the result.
This formula's final form is:
=IF(COUNTIF(A2:A6,”*”&B2”*”), ”YES”, ”NO”) |
Where,
- The range is A2:A6.
- "*"&B2&"*" is the criterion.
- The result will show "YES" if the value is found.
- The result will show "NO" if the value is not found.
Output:
IF with ISNUMBER Function
We will apply the IF formula with the ISNUMBER function to cell C2, where we want the result.
This formula's final form is:
=IF(ISNUMBER(SEARCH(B2, A2)), ”FOUND”, ”NOT FOUND”) |
Where,
- We will find the text B2 in A2 using the SEARCH function
- The result will show "FOUND" if the value is found.
- The result will show "NOT FOUND" if the value is not found.
Output:
IF with OR Function
The IF with OR function formula can also be used to find a certain text from a range of cells. In cell E4, we now use the IF with OR formula.
This formula's final form is:
=IF(OR(COUNTIF(A2,”*”&$B$2:$B$6&”*”)), ”YES”, ”NOT FOUND”) |
Where,
- The range is A2.
- ”*”&$B$2:$B$6&”*” is the criterion.
- The result will show "YES" if the value is found.
- The result will show "NOT FOUND" if the value is not found.
Output:
Using SUMPRODUCT Function
We can use the SUMPRODUCT function to discover specific texts in a group of cells. To learn, follow the below instructions.
In cell C2, use the SUMPRODUCT function. The COUNTIF function is nested within the SUMPRODUCT function in this case.
The final formula is as follows:
=SUMPRODUCT(COUNTIF(A2:A6,”*”&B2&”*”))>0 |
Where,
- The range is A2:A6.
- ”*”&B2&”*” is the criterion.
- The COUNTIF function counts the number of cells that have been matched.
- The COUNTIF method returns a number, which the SUMPRODUCT function sums together.
Output: