Conditional Formatting for Partial Text Match in Excel (9 Examples)

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.

Download Workbook


9 Ways to Apply Conditional Formatting for Partial Text Match in Excel

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.

Excel conditional formatting partial text match

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.


Method-1: Using ‘Text that Contains’ Option for Highlighting Partial Text Matches

Here, we will highlight the items which have a part 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.

Excel conditional formatting partial text match

Steps:
➤ Select the range and then go to the Home Tab >> Styles Group >> Conditional Formatting Dropdown >> Highlight Cells Rules Option >> Text that Contains 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.

Text that Contains Option

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.

Excel conditional formatting partial text match

Read More: How to Highlight Partial Text in Excel Cell (9 Methods)


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.

Excel conditional formatting partial text match

Step-01:
➤ Select the range and then go to the Home Tab >> Styles Group >> Conditional Formatting Dropdown >> New Rule Option.

SEARCH function

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.

SEARCH function

After that, the Format Cells dialog box will open up.
➤ Select the Fill Option, choose any Background Color and then click on OK.

SEARCH function

Afterward, you will be taken to the New Formatting Rule dialog box again.

Excel conditional formatting partial text match

Step-02:
➤ Type the following formula in the Format values where this formula is true box

=SEARCH("apple",$B4)>0

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 for finding the matches it will return a value greater than 0.

➤ Press OK.

SEARCH function

Finally, you will get the cells highlighted for having a partial match with Apple or apple.

Excel conditional formatting partial text match


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.

Excel conditional formatting partial text match

Steps:
➤ Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule Dialog Box.

SEARCH & ISNUMBER functions

➤ 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.

SEARCH & ISNUMBER functions

Finally, we will be able to apply Conditional Formatting to those cells of the Item column which have a part of the whole text as Apple or apple.

SEARCH & ISNUMBER functions

Read More: How to Use IF Partial Match in Excel (4 Basic Operations)


Similar Readings


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.

Excel conditional formatting partial text match

Steps:
➤ Follow Step-01 of Method-2.
Then, you will get the following New Formatting Rule dialog box.

Case-Sensitive Partial Match

➤ 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.

Case-Sensitive Partial Match

Eventually, we highlighted the cells of the Item column having the texts Sugar Apple, Rose Apple, and Custard Apple.

Excel conditional formatting partial text match


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.

Excel conditional formatting partial text match

Steps:
➤ Follow Step-01 of Method-2.
Afterward, you will get the following New Formatting Rule dialog box.

COUNTIF Function

➤ 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.

COUNTIF Function

Eventually, we successfully applied Conditional Formatting to the cells having a portion of Apple or apple in the Item column.

Excel conditional formatting partial text match

Read More: COUNTIF Partial Match in Excel (2 or More Approaches)


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.

Excel conditional formatting partial text match

Steps:
➤ Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule dialog box.

COUNT & SEARCH Functions

➤ 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. And then, COUNT will return 1 if it gets any number from the output of the SEARCH function otherwise 0.

➤ Press OK.

COUNT & SEARCH Functions

Finally, you will be able to apply Conditional Formatting to those cells of the Item column which have a part Apple or apple of the whole text.

COUNT & SEARCH Functions


Similar Readings


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.

Excel conditional formatting partial text match

Steps:
➤ Follow Step-01 of Method-2.
Afterward, you will get the following New Formatting Rule dialog box.

IF & SEARCH functions

➤ Write down the following formula in the Format values where this formula is true box

=IF(SEARCH("apple",$B4),1,0)>0

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 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.

IF & SEARCH functions

Finally, you will get the cells highlighted for having a partial match with Apple or apple.

Excel conditional formatting partial text match

Read More: How to Perform Partial Match String in Excel (5 Methods)


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.

Excel conditional formatting partial text match

Steps:
➤ Follow Step-01 of Method-2.
After that, you will get the following New Formatting Rule dialog box.

MATCH Function

➤ 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.

MATCH Function

Eventually, we successfully applied Conditional Formatting to the cells having a portion of Apple or apple in the Item column.

Excel conditional formatting partial text match


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.

Excel conditional formatting partial text match

Steps:
➤ Follow Step-01 of Method-2.
Afterward, you will get the following New Formatting Rule dialog box.

Multiple partial matches

➤ 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.

Multiple partial matches

Finally, we are getting the highlighted cells for partial matches with either apple or berries.

Multiple partial matches

Read More: How to Use Formula for Partial Number Match in Excel (5 Examples)


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.

Practice


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.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo