Home / Excel Formulas List / Check IF a Cell Contains a Partial Text (Formula in Excel)
Contents hide
Excel Formula: IF a Cell Contains a Partial Text
IF a Cell Contains a Partial Text (SEARCH + ISNUMBER)
Get the Excel File
Related Formulas
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.
![Check IF a Cell Contains a Partial Text (Formula in Excel) (1) Check IF a Cell Contains a Partial Text (Formula in Excel) (1)](https://i0.wp.com/cdn-amgoo.nitrocdn.com/qJvQlgGQEOwNXyhUqNwiAWOQgCDvoMdJ/assets/images/optimized/rev-f5d5dbb/excelchamps.com/wp-content/uploads/2022/12/1-check-if-cell-has-partial-text.png)
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.
![Check IF a Cell Contains a Partial Text (Formula in Excel) (2) Check IF a Cell Contains a Partial Text (Formula in Excel) (2)](https://i0.wp.com/cdn-amgoo.nitrocdn.com/qJvQlgGQEOwNXyhUqNwiAWOQgCDvoMdJ/assets/images/optimized/rev-f5d5dbb/excelchamps.com/wp-content/uploads/2022/12/2-if-formula-to-find-partial-text.png)
=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.
![Check IF a Cell Contains a Partial Text (Formula in Excel) (3) Check IF a Cell Contains a Partial Text (Formula in Excel) (3)](https://i0.wp.com/cdn-amgoo.nitrocdn.com/qJvQlgGQEOwNXyhUqNwiAWOQgCDvoMdJ/assets/images/optimized/rev-f5d5dbb/excelchamps.com/wp-content/uploads/2022/12/3-partial-value-results.png)
To understand this formula, you need to break it up into two parts:
![Check IF a Cell Contains a Partial Text (Formula in Excel) (4) Check IF a Cell Contains a Partial Text (Formula in Excel) (4)](https://i0.wp.com/cdn-amgoo.nitrocdn.com/qJvQlgGQEOwNXyhUqNwiAWOQgCDvoMdJ/assets/images/optimized/rev-f5d5dbb/excelchamps.com/wp-content/uploads/2022/12/4-break-formula-into-two-parts.png)
- 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.
![Check IF a Cell Contains a Partial Text (Formula in Excel) (5) Check IF a Cell Contains a Partial Text (Formula in Excel) (5)](https://i0.wp.com/cdn-amgoo.nitrocdn.com/qJvQlgGQEOwNXyhUqNwiAWOQgCDvoMdJ/assets/images/optimized/rev-f5d5dbb/excelchamps.com/wp-content/uploads/2022/12/5-if-search-isnumber-to-find-partial-text.png)
=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”.