To check if a cell contains a partial text in Excel, you need to create a formula, with the help of IF, COUNTIF, and wildcard characters. Wildcard characters help you define the partial text, then COUNTIF checks for partial value, and IF returns the result according to that. This combination of functions works perfectly.
In this tutorial, we will learn to write this formula.
Excel Formula: IF a Cell Contains a Partial Text
- First, in cell B1 enter “=IF(“.
- After that, enter “COUNTIF(“.
- Next, in the first argument of COUNTIF, refer to cell A1.
- Now, enter the value that you want to check by using an asterisk before and after that value and close the function.
- In the end, enter “Y” for value_if_true, “N” for value_if_false” enter closing parentheses, and hit enter.
=IF(COUNTIF(A1,”*Excel*”),”Y”,”N”)
In the following example, you can see in the first value you have “Excel”, and it has returned “Y”. But in the second value, you don’t have that value, and he has returned “N” in the result.
To understand this formula, you need to break it up into two parts:
- In the first part, we have COUNTIF uses wildcard characters that check for the partial value from the cell. And if the value is there, it returns 1 in the result, and if not, it returns 2.
- In the second part, IF takes the result of COUNTIF and uses 1 as TRUE and 0 as FALSE. So, if it’s TRUE it returns Y, and if it’s FALSE then N.
IF a Cell Contains a Partial Text (SEARCH + ISNUMBER)
You can also use a formula combining SEARCH, ISNUMBER, and IF to check if a partial value is there in a cell.
=IF(ISNUMBER(SEARCH(“Excel*”, A1)),”Y”,”N”)
This formula uses SEARCH and Wildcards to check for the value. If the value is there, it returns a number and if the value is not there, it returns an #VALUE! error.
To manage this error, you need to use ISNUMBER and IF. ISNUMBER checks if the result from SEARCH is a number or not and returns TRUE or FALSE.
Now, if the result from ISNUMBER is TRUE, IF returns “Y” and if it’s FALSE then IF returns “N”.