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
Below is the formula and steps to write this formula using a combination of COUNTIF and IF in a single formula:
- 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.
If “Excel” is part of the text in cell A1 (like “Excel Champs”, “XLChamps”, etc.), COUNTIF returns a number greater than 0. Since any number greater than 0 is treated as TRUE in logical tests, IF then returns “Y”. If “Excel” is not in cell A1, COUNTIF returns 0, which is treated as FALSE in logical tests, and IF returns “N”.
Wildcard Characters – In Excel, wildcard characters are special symbols that represent one or more unspecified characters in your searches or when setting conditions in formulas. An asterisk (*) matches any sequence of characters. A Question Mark (?) matches any single character. A tilde (~) lets you search for items that actually contain an asterisk, question mark, or tilde.
To understand this formula, you need to break it up into two parts:
- COUNTIF – This function counts the number of cells that meet a specific criterion. In this formula, the criterion is that cell A1 must contain the substring “Excel”.
- Range – A1 – The cell to check.
- “Excel” – An asterisk (*) is a wildcard character in Excel that represents any sequence of characters. Including them before and after “Excel” means the formula looks for any occurrence of “Excel” anywhere within the cell’s value.
- IF – IF is used for a conditional check. It evaluates the result of the COUNTIF function and decides what to return based on that result.
- COUNTIF(A1, “Excel”) – This part of the formula calculates the number of times “Excel” appears in cell A1. If “Excel” appears at least once, COUNTIF returns a number greater than 0.
- Value_if_true: “Y” – If the COUNTIF function finds one or more occurrences of “Excel”, the IF returns “Y”.
- Value_if_false: “N” – If the COUNTIF function returns 0 (meaning “Excel” is not found), the IF returns “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") - SEARCH: This searches for text string within another text string and returns the position of the first character of the first text string. It is case-insensitive.
- “Excel” – The asterisks (*) is used as wildcards to indicate any sequence of characters before and after “Excel”.
- A1 – This specifies that SEARCH should look within the contents of cell A1.
- ISNUMBER: It checks whether the result of the SEARCH function is a number. This part of the formula evaluates to TRUE if “Excel” is found (because SEARCH returns a number indicating the starting position of “Excel”), and FALSE if “Excel” is not found (because SEARCH results in an error, which is not a number).
- IF: It uses the result from ISNUMBER. If ISNUMBER is TRUE (meaning “Excel” was found), the IF function returns “Y”. If ISNUMBER is FALSE (meaning “Excel” was not found), the IF function returns “N”.
Get the Excel File
With FILTER Function (Excel 365 and Excel 2019)
If you need to filter out all rows that contain the partial text in a separate range, you can use the FILTER function with the wildcard characters, like the following:
=FILTER(A2:A100, ISNUMBER(SEARCH("Excel", A2:A100))) Here’s how each part of the formula works:
- SEARCH(“Excel”, A2:A100): This searches for the string “Excel;” within each cell in the range A2 to A100. If “Excel” is found in a cell, SEARCH returns the starting position of the text as a number. If it is not, SEARCH returns an error (#VALUE!).
- ISNUMBER(SEARCH(“Excel”, A2:A100)): It checks whether the SEARCH function’s result is a number. ISNUMBER returns TRUE for cells where SEARCH found “Excel” (since it returns a numeric position) and FALSE for cells where SEARCH did not find “Excel” (since it results in an error).
- FILTER(A2:A100, ISNUMBER(SEARCH(“partial text”, A2:A100))): The FILTER function takes the range A2:A100 as the data to filter. The second argument, ISNUMBER(SEARCH(“partial text”, A2:A100)), serves as the condition for filtering. Only those cells in A2 to A100 where ISNUMBER returns TRUE (meaning “Excel” was found within them) will display as the FILTER function output.
Use Conditional Formatting to Identify the Partial Text
Here’s steps on how to use conditional formatting in Excel to highlight cells that contain specific partial text:
- Select the Range – Select the range of cells where you want to apply the conditional formatting. For example, select A1:A10 if you want to check all these cells.
- Open the Conditional Formatting: Go to the Home tab on the Ribbon. Click on Conditional Formatting. Hover over Highlight Cells Rules. Click on “Text that Contains…”
- Set the Text Condition: A dialog box will appear where you can specify the text that triggers the formatting. In the box, enter the partial text you want to search for. For example, type “Excel” to highlight cells containing the Text “Excel.”
- Set Format – You can choose the formatting style after entering the text. Click on the drop-down menu, which shows different formatting options like changing the cell fill color, text color, or filling pattern. Select a format that makes the cells stand out. For example, you might choose a light red fill with dark red text.
- Apply and Close – Click OK to apply the conditional formatting. The cells in your selected range that contain the specified partial text will now be highlighted with the formatting you chose.
Note – If you want to apply conditions for multiple values, repeat the process for each text condition.