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.

**Table of Contents**hide

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

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***, and**

*Rose Apple***irrespective of the case of this text part with the help of the**

*Custard Apple***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**.

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

** 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)>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

**in the full text and so for finding the matches it will return a value greater than**

*apple***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

**or**

*Apple***as part of the texts.**

*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

`=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

**in the full text. And so**

*apple***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 which have a part of the whole text as **Apple **or **apple**.

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

**Similar Readings**

**How to Use Partial VLOOKUP in Excel(3 or More Ways)****Use INDEX and Match for Partial Match (2 Ways)****Excel Partial Match Two Columns (4 Simple Approaches)**

__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

**in the full text. For not matching with the cases of**

*Apple***properly, we will not get any value.**

*Apple*➤ Press **OK**.

Eventually, we highlighted the cells of the **Item **column having the texts ** Sugar Apple**,

**, and**

*Rose Apple***.**

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

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

**or**

*apple***.**

*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

**in the full text. And then,**

*Apple***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 which have a part **Apple **or **apple **of the whole text.

**Similar Readings**

**How to Use VLOOKUP for Partial Match in Excel (4 Ways)****Lookup Partial Text Match in Excel (5 Methods)****VLOOKUP Partial Text from a Single Cell in Excel**

__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)>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

**in the full text. And then,**

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

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

** 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 portionin cell*apple***B4**, and for finding any matches it will return a value which will be the starting position of thein the full text otherwise*apple***#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 portionin cell*berries***B4**, and for finding any matches it will return a value which will be the starting position of thein the full text otherwise*berries***#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***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.

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