If you are searching for some of the ways to apply conditional formatting for partial text match in Excel, then you will find this article useful. So, let’s start our main article.
How to Apply Conditional Formatting for Partial Text Match in Excel: 9 Ways
In the following dataset, we have some sales records for some items of a company. Among the items we can see that we have a portion of the text Apple is common for some items and similarly, there are some items named with berries.
So, using the following 9 methods we will highlight the items with partial text matches.
We have used the Microsoft Excel 365 version here, you can use any other version according to your convenience.
Method-1: Using ‘Text that Contains’ Option for Highlighting Partial Text Matches
Here, we will highlight the items that have a part of Apple such as Pineapple, Sugar Apple, Rose Apple, and Custard Apple irrespective of the case of this text part with the help of the Text that Contains option of Conditional Formatting.
Steps:
➤ Select the range and then go to the Home tab >> Styles Group >> Conditional Formatting Dropdown >> Highlight Cells Rules Option >> Text that Contains… Option.
Then, the Text That Contains dialog box will open up.
➤ Write apple in the first box and select your desired formatting style (here, Light Red Fill with Dark Red Text style has been selected) in the second box.
➤ Press OK.
As a result, you will be able to apply Conditional Formatting to the cells of the Item column having a partial match with Apple or apple.
Method-2: Using SEARCH Function
In this section, we will be using the SEARCH function in Conditional Formatting to highlight the cells for partial text matches containing Apple or apple.
Step-01:
➤ Select the range and then go to the Home tab >> Styles Group >> Conditional Formatting Dropdown >> New Rule Option.
Then, the New Formatting Rule wizard will appear.
➤ Select Use a formula to determine which cells to format option and click on the Format Option.
After that, the Format Cells dialog box will open up.
➤ Select the Fill Option, choose any Background Color, and then click on OK.
Afterward, you will be taken to the New Formatting Rule dialog box again.
Step-02:
➤ Type the following formula in the Format values where this formula is true box
=SEARCH("apple",$B4)>0SEARCH will look for the portion apple in the cells of Column B and for finding any matches it will return a value which will be the starting position of the apple in the full text and so for finding the matches, it will return a value greater than 0.
➤ Press OK.
Finally, you will get the cells highlighted for having a partial match with Apple or apple.
Method-3: Using SEARCH and ISNUMBER Functions
In this section, we are going to use the combination of the SEARCH function and ISNUMBER function to apply Conditional Formatting to the cells of the Item column having Apple or apple as part of the texts.
Steps:
➤ Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule Dialog Box.
➤ Type the following formula in the Format values where this formula is true box
=ISNUMBER(SEARCH("apple",$B4))SEARCH will look for the portion apple in the cells of Column B and for finding any matches it will return a value which will be the starting position of the apple in the full text. And so ISNUMBER will return a TRUE if it gets any numeric value otherwise FALSE.
➤ Press OK.
Finally, we will be able to apply Conditional Formatting to those cells of the Item column that have a part of the whole text as Apple or apple.
Method-4: Conditional Formatting for Case-Sensitive Partial Text Match Using FIND Function
For highlighting the texts with case-sensitive partial matches for Apple we will use the FIND function in the Conditional Formatting here.
Steps:
➤ Follow Step-01 of Method-2.
Then, you will get the following New Formatting Rule dialog box.
➤ Write down the following formula in the Format values where this formula is true box
=FIND("Apple",$B4)FIND will look for the portion Apple in the cells of Column B and for finding any matches it will return a value which will be the starting position of the Apple in the full text. For not matching with the cases of Apple properly, we will not get any value.
➤ Press OK.
Eventually, we highlighted the cells of the Item column having the texts Sugar Apple, Rose Apple, and Custard Apple.
Method-5: Using COUNTIF Function for Checking Partial Text Match
In this section, we are going to apply Conditional Formatting with the help of the COUNTIF function for partial text match in Excel.
Steps:
➤ Follow Step-01 of Method-2.
Afterward, you will get the following New Formatting Rule dialog box.
➤ Type the following formula in the Format values where this formula is true box
=COUNTIF($B4,"*apple*")By using the wildcard symbol * before and after apple we are ensuring the partial matches here and COUNTIF will return the number of times this text portion appears in the cells of Column B.
➤ Press OK.
Eventually, we successfully applied Conditional Formatting to the cells having a portion of Apple or apple in the Item column.
Method-6: Using Combination of COUNT and SEARCH Functions
Here, we will be using the combination of the COUNT function and SEARCH function to apply Conditional Formatting to the cells having partial text matches with apple or Apple.
Steps:
➤ Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule dialog box.
➤ Type the following formula in the Format values where this formula is true box
=COUNT(SEARCH("Apple",$B4))SEARCH will look for the portion Apple in the cells of Column B and for finding any matches it will return a value which will be the starting position of the Apple in the full text. Then, COUNT will return 1 if it gets any number from the output of the SEARCH function otherwise 0.
➤ Press OK.
Finally, you will be able to apply Conditional Formatting to those cells of the Item column that have a part Apple or apple of the whole text.
Method-7: Using Combination of IF and SEARCH Functions
In this section, we will be using the combination of the IF function and SEARCH function in Conditional Formatting to highlight the cells for partial text matches containing Apple or apple.
Steps:
➤ Follow Step-01 of Method-2.
Afterward, you will get the following New Formatting Rule dialog box.
➤ Write down the following formula in the Format values where this formula is true box
=IF(SEARCH("apple",$B4),1,0)>0SEARCH will look for the portion apple in the cells of Column B and for finding any matches it will return a value which will be the starting position of the apple in the full text. And then, IF will return 1 if SEARCH finds any matches otherwise 0 and for values greater than 0 finally, we will get TRUE otherwise FALSE.
➤ Press OK.
Finally, you will get the cells highlighted for having a partial match with Apple or apple.
Method-8: Conditional Formatting for Partial Text Match Using MATCH Function
We will use the MATCH function in Conditional Formatting for the partial matches with Apple or apple in the Item column.
Steps:
➤ Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule dialog box.
➤ Type the following formula in the Format values where this formula is true box
=MATCH("*apple*",$B4,0)By using the wildcard symbol * before and after apple we are ensuring the partial matches here and MATCH will return 1 for finding any partial matches in Column B.
➤ Press OK.
Eventually, we successfully applied Conditional Formatting to the cells having a portion of Apple or apple in the Item column.
Method-9: Conditional Formatting for Multiple Partial Text Match Using Combined Formula
For highlighting the partial matches with apple or berries in the Item column, here we will use the combination of the IF function, OR function, ISNUMBER function, and SEARCH function in Conditional Formatting.
Steps:
➤ Follow Step-01 of Method-2.
Afterward, you will get the following New Formatting Rule dialog box.
➤ Type the following formula in the Format values where this formula is true box
=IF(OR(ISNUMBER(SEARCH("apple", $B4)), ISNUMBER(SEARCH("berries", $B4))), "Yes", "")="Yes"- SEARCH(“apple”, $B4) → SEARCH will look for the portion apple in cell B4, and for finding any matches it will return a value which will be the starting position of the apple in the full text otherwise #N/A.
Output → #N/A
- ISNUMBER(SEARCH(“apple”, $B4)) becomes
ISNUMBER(#N/A) → ISNUMBER will return TRUE for any numeric value otherwise FALSE.
Output → FALSE
- SEARCH(“berries”, $B4) → SEARCH will look for the portion berries in cell B4, and for finding any matches it will return a value which will be the starting position of the berries in the full text otherwise #N/A.
Output → #N/A
- ISNUMBER(SEARCH(“berries”, $B4)) becomes
ISNUMBER(#N/A) → ISNUMBER will return TRUE for any numeric value otherwise FALSE.
Output → FALSE
- OR(ISNUMBER(SEARCH(“apple”, $B4)), ISNUMBER(SEARCH(“berries”, $B4))) becomes
OR(FALSE, FALSE) → OR will return TRUE if any of the values are TRUE otherwise FALSE.
Output → FALSE
- IF(OR(ISNUMBER(SEARCH(“apple”, $B4)), ISNUMBER(SEARCH(“berries”, $B4))), “Yes”, “”) becomes
IF(FALSE, “Yes”, “”) → IF will return yes for TRUE and a blank for FALSE.
Output Blank
- IF(OR(ISNUMBER(SEARCH(“apple”, $B4)), ISNUMBER(SEARCH(“berries”, $B4))), “Yes”, “”)=”Yes” becomes
“”=”Yes” → returns TRUE for matching the two values otherwise FALSE.
Output → FALSE
➤ Press OK.
Finally, we are getting the highlighted cells for partial matches with either apple or berries.
Practice Section
For doing practice by yourself we have provided a practice section like below in a sheet named practice. Please do it by yourself.
Download Workbook
Conclusion
In this article, we tried to apply Conditional Formatting for partial text match in Excel easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.
<< Go Back to Partial Match Excel | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
