The article will show you some easy process on how to highlight **partial text **in an Excel cell. Sometimes, you may be required to recognize similar types of data in an Excel chart and therefore you need to highlight the cells containing similar types of data.

In the dataset, we have some guys’ **names **and their official **ID No**.

**Table of Contents**hide

## Download Practice Workbook

## 9 Ways to Highlight Partial Text in Excel Cell

**1. Using SEARCH Function to Highlight Partial Text in Excel Cell**

The easiest trick to highlight **partial text **in an Excel cell is to use **the SEARCH Function** in the **Conditional Formatting **section. Suppose you want to **highlight the cells** in the **ID No column **which contain **two zeros **(**00**) anywhere in an **ID No**. Let’s discuss the process below.

**Steps:**

- Select the
**range C5:C13**and go to**Home**>>**Conditional Formatting**>>**New Rule**

- The
**New Formatting Rule**window will appear. Choose**Use a formula to determine which cells to format**. - Type the following formula in
**Format values where this formula is true**

`=SEARCH(“00”,C5)`

- After that, click on
**Format**.

Here the **SEARCH Function **selects the cells through **C5 **to **C13 **that contain **double zeros **at anywhere of **ID No**.

- Now, you have the materials to format your desired cells. In this case, I just want to
**highlight the partial cells**containing**two zeros**at the beginning with**blue**So I select**Fill**>>**Blue Color**and then click**OK**.

- After that, you will see a preview of how your desired cells will look like. Just click
**OK.**

You will see the **ID No **that contains **double zeros **are filled with **blue color**.

Thus, you can easily **highlight** partial text in an Excel cell.

**Read More: Conditional Formatting for Partial Text Match in Excel (9 Examples)**

**2. Applying COUNTIF Function to Highlight Partial Text**

You can also use **the COUNTIF Function** to highlight an Excel cell that contains the **partial text**. Suppose you want to **highlight the partial text** of the **ID No column **which starts with **two zeros **(**00**) at the beginning. Let’s discuss the process below.

**Steps:**

- Store ‘
**00-**’ in a new cell as Excel considers ‘**00**’ as a single-digit number ‘**0**’.

- Select the
**range C5:C13**and go to**Home**>>**Conditional Formatting**>>**New Rule**

- The
**New Formatting Rule**window will appear. Select**Use a formula to determine which cells to format**. - Type the following formula in
**Format values where this formula is true**

`=COUNTIF(“00”,LEFT(C5,3))`

- After that, click on
**Format**.

Here the **COUNTIF Function **will return the number of times the text part in cell **E5 **appears in the cells of** Column B**

- Now, you have the materials to format your desired cells. In this case, I just want to highlight the cells starting with
**two zeros**at the beginning with**blue**So I select**Fill**>>**Blue Color**and then click**OK**.

- After that, you will see a preview of how your desired cells will look like. Just click
**OK**.

You will see the **ID No **that starts with **double zeros **are filled with **blue color**.

Thus, you can easily highlight **partial text **in an Excel cell.

**Read More: Excel Partial Match Two Columns (4 Simple Approaches)**

**3. Utilizing COUNT and SEARCH Functions to Highlight Partial Text**

You can also combine the **COUNT** and **SEARCH Functions **to **highlight** an Excel cell that contains the **partial text**. Suppose you want to **highlight the partial text** of the **ID No column **which starts with **two zeros **(**00**) at the beginning. Let’s go through the process below.

**Steps:**

- Store ‘
**00-**’ in a new cell as Excel considers ‘**00**’ as a single-digit number ‘**0**’.

- Select the
**range C5:C13**and go to**Home**>>**Conditional Formatting**>>**New Rule**

- The
**New Formatting Rule**window will appear. Select**Use a formula to determine which cells to format**. - Type the following formula in
**Format values where this formula is true**

`=COUNT(SEARCH($E$5,C5))`

- After that, click on
**Format**.

Here **the** **SEARCH Function** returns the position of text part ‘**00-**’ through **column C **and **the COUNT Function** counts how many times this position appears.

- Now, you have the materials to format your desired cells. In this case, I just want to highlight the cells starting with
**two zeros**at the beginning with**blue**So I select**Fill**>>**Blue Color**and then click**OK**.

- After that, you will see a preview of how your desired cells will look like. Just click
**OK.**

This operation will highlight the cells that have **partial text **‘**00-**’ with **blue color**..

Thus, you can easily highlight **partial text **in an Excel cell.

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

**4. Using ISNUMBER and SEARCH Functions to Highlight Partial Text**

Now we are going to combine the **ISNUMBER** and **SEARCH Functions **to highlight an Excel cell that contains the **partial text**. Suppose you want to **highlight the partial text** of the **ID No column **which starts with **two zeros **(**00**) at the beginning. Let’s go through the process below.

**Steps:**

- Store ‘
**00-**’ in a new cell as Excel considers ‘**00**’ as a single-digit number ‘**0**’.

- Select the
**range C5:C13**and go to**Home**>>**Conditional Formatting**>>**New Rule**

- The
**New Formatting Rule**window will appear. Select**Use a formula to determine which cells to format**. - Type the following formula in
**Format values where this formula is true**

`=ISNUMBER(SEARCH($E$5,$C5))`

- After that, click on
**Format**.

Here **the SEARCH Function** will search for the text part **00-** in the **range C5:C13** and it will return the position of **00-** from those texts if it finds any match. The **ISNUMBER** function will return** TRUE** if it gets any numeric value otherwise it will return **FALSE**.

- Now, you have the materials to format your desired cells. In this case, I just want to highlight the cells starting with
**two zeros**at the beginning with**blue**So I select**Fill**>>**Blue Color**and then click**OK**.

- After that, you will see a preview of how your desired cells will look like. Just click
**OK**.

This operation will highlight the cells that have **partial text **‘**00-**’ with **blue color**..

Thus, you can easily highlight **partial text **in an Excel cell.

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

**Similar Readings**

**How to Use INDEX and Match for Partial Match (2 Ways)****Use VLOOKUP for Partial Match in Excel (4 Ways)****VLOOKUP Partial Text from a Single Cell in Excel****Excel VLOOKUP to Find the Closest Match (with 5 Examples)**

**5. Utilizing FIND Function to Highlight Partial Text**

Another easy way to highlight **partial text is** to use **the FIND Function**. Suppose you want to **highlight the partial text** of the **ID No column **which starts with **two zeros **(**00**) at the beginning. Let’s go through the process below.

**Steps:**

- Select the
**range C5:C13**and go to**Home**>>**Conditional Formatting**>>**New Rule**

- The
**New Formatting Rule**window will appear. Select**Use a formula to determine which cells to format**. - Type the following formula in
**Format values where this formula is true**

`=FIND("00-",$C5)`

- After that, click on
**Format**.

Here the **FIND Function **returns the position of **00- **from the **range C5:C13**.

- Now, you have the materials to format your desired cells. In this case, I just want to highlight the cells starting with
**two zeros**at the beginning with an**orange**So I select**Fill**>>**Orange Color**and then click**OK**.

- After that, you will see a preview of how your desired cells will look like. Just click
**OK**.

This operation will highlight the cells that have **partial text **‘**00-**’ with **orange color**..

Thus, you can easily highlight **partial text **in an Excel cell.

**Read More: How to Find Partial Match in Two Columns in Excel (4 Methods)**

**6. Combining IF and SEARCH Functions**

Now we are going to combine the **IF** and **SEARCH Functions **to highlight an Excel cell that contains the **partial text**. Suppose you want to **highlight the partial text** of the **ID No column **which starts with **two zeros **(**00**) at the beginning. Let’s go through the process below.

**Steps:**

- Select the
**range C5:C13**and go to**Home**>>**Conditional Formatting**>>**New Rule**

- The
**New Formatting Rule**window will appear. Select**Use a formula to determine which cells to format**. - Type the following formula in
**Format values where this formula is true**

`=IF(SEARCH("00-",$C5),1,0)>0`

- After that, click on
**Format**.

Here the **SEARCH Function **will look for the part **00-**** **in

**C5:C13**and will return its position from that

**range**if it finds any match. And then, the

**IF Function**will return

**1**if the

**SEARCH f**unction finds any match otherwise it will return

**0**and also for values greater than

**0**.

- Now, you have the materials to format your desired cells. In this case, I just want to highlight the cells starting with
**two zeros**at the beginning with**orange**So I select**Fill**>>**Orange Color**and then click**Ok**.

- After that, you will see a preview of how your desired cells will look like. Just click
**OK**.

This operation will highlight the cells that have **partial text **‘**00-**’ with **orange color**..

Thus, you can easily highlight **partial text **in an Excel cell.

**Read More:** **How to Use IF Function to Find Partial Match in Excel (6 Ways)**

**7. Applying MATCH Function to Highlight Partial Text**

Now we are going to use **the MATCH Function** to highlight an Excel cell that contains the **partial text**. Suppose you want to **highlight the partial text** of the **ID No column **which starts with **two zeros **(**00**) at the beginning. Let’s go through the process below.

**Steps:**

- Select the
**range C5:C13**and go to**Home**>>**Conditional Formatting**>>**New Rule**

- The
**New Formatting Rule**window will appear. Select**Use a formula to determine which cells to format**. - Type the following formula in
**Format values where this formula is true**

`=MATCH("*00-*",$C5,0)`

- After that, click on
**Format**.

We are ensuring the **partial matches** here by using the **wildcard **symbol (*****) within the text part **00-** and the **MATCH function** will return 1 if it gets any partial matches in **C5:C13**.

- Now, you have the materials to format your desired cells. In this case, I just want to highlight the cells starting with
**two zeros**at the beginning with**green**So I select**Fill**>>**Green Color**and then click**OK**.

- After that, you will see a preview of how your desired cells will look like. Just click
**OK**

This operation will highlight the cells that have **partial text **‘**00-**’ with **green color**..

Thus, you can easily highlight **partial text **in an Excel cell.

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

**8. Use of Combined Formula**

Suppose you want to **highlight the partial text** of the **ID No column **which starts with **two zeros **(**00**) or **01 **at the beginning. You can combine **IF**, **OR**, **ISNUMBER**, and **SEARCH Functions **for this purpose. Let’s go through the process below.

**Steps:**

- Select the
**range C5:C13**and go to**Home**>>**Conditional Formatting**>>**New Rule**

- The
**New Formatting Rule**window will appear. Select**Use a formula to determine which cells to format**. - Type the following formula in
**Format values where this formula is true**

`=IF(OR(ISNUMBER(SEARCH("00-", $C5)), ISNUMBER(SEARCH("01-", $C5))), "Yes", "")="Yes"`

- After that, click on
**Format**.

**Formula Breakdown**

**SEARCH(“00-”, $C5) —-> SEARCH**function will return the position of the text ‘**00-**’ from the**ID No**in cell**C5**if it finds a match otherwise it will return**#N/A**(**Value not Available Error**).

Output**: 1**

**ISNUMBER(SEARCH(“00-”, $C5))**becomes

ISNUMBER(1) —-> ISNUMBER returns**TRUE**for any numeric value, else, it returns**FALSE**.

Output:**TRUE****SEARCH(“01-”, $C5) —->**Turns into

Output**: #N/A****ISNUMBER(SEARCH(“01-”, $C5)) —->**becomes

ISNUMBER(#N/A) —-> returns

Output**: FALSE****OR(ISNUMBER(SEARCH(“00-”, $C5)), ISNUMBER(SEARCH(“01-”, $C5))) —->**Turns into

OR(TRUE, FALSE) —-> OR function returns**TRUE**if any of the values are**TRUE**otherwise it results in**FALSE**.

Output:**TRUE****IF(OR(ISNUMBER(SEARCH(“00-”, $C5)), ISNUMBER(SEARCH(“01-”, $C5))), “Yes”, “”) —->**becomes

IF(TRUE, “Yes”, “”) →**IF**will return**yes**for**TRUE**and a**blank**for**FALSE**.

Output:**Yes****IF(OR(ISNUMBER(SEARCH(“00-”, $C5)), ISNUMBER(SEARCH(“01-”, $C5))), “Yes”, “”)=”Yes” —->**turns into

“Yes”=”Yes” —-> returns**TRUE**the**two**values match with each other but on the contrary, it returns**FALSE**.

Output →**TRUE**

- Now, you have the materials to format your desired cells. In this case, I just want to highlight the cells starting with
**two zeros**(**00**) or**01**at the beginning with**pink**So I select**Fill**>>**Pink Color**and then click**OK**.

- After that, you will see a preview of how your desired cells will look like. Just click
**OK**.

This operation will highlight the cells that have **partial text **‘**00-**’ and ‘**01-**’with **pink color**.

Thus, you can easily highlight **partial text **in an Excel cell.

**Read more:** **How to Perform Partial Match of String in Excel (8 Easy Methods)**

**9. Application of VBA**

We can also use **Microsoft Visual Basic for Applications **(**VBA**) to **highlight partial text **in an Excel cell. We want to change the **font color **of **Ben **and **Frank **to red. Let’s see how we can do this by **VBA**.

**Steps:**

- First, open
**Visual Basic**from the**Developer Tab**.

- The
**VBA**window will open. Select**Insert**>>**Module**.

- Type the following code in the
**VBA Module**.

```
Sub Highlight_Partial_Text()
Dim row_number As Integer, col_number As Integer
Dim Partial_Text_Cell As String
col_number = 2
For row_number = 5 To 13
Partial_Text_Cell = ActiveSheet.Cells(row_number, col_number).Value
TextPosition1 = InStr(1, Partial_Text_Cell, "Ben")
TextPosition2 = InStr(1, Partial_Text_Cell, "Frank")
If TextPosition1 > 0 Then
ActiveSheet.Cells(row_number, col_number).Characters(TextPosition1, 3).Font.Color = RGB(255,0,0)
End If
If TextPosition2 > 0 Then
ActiveSheet.Cells(row_number, col_number).Characters(TextPosition2, 5).Font.Color = RGB(255,0,0)
End If
Next row_number
End Sub
```

Here, we define **row_number** and **col_number **as Integer variables and **Partial_Text_Cell **as a **string **variable. Then we define the **Text Positions **of **Ben **and **Frank**. Later, we used the **IF **Statement within the **For Loop** to change their **font color **by **VBA Font.Color property**.

- Now go back to your
**worksheet**and run**Macros**.

- After that, you will see the name
**Ben**and**Frank**with the font color**red**.

Thus you can **highlight partial text **in excel cells using **VBA**.

## Practice Section

You will find the dataset of this article in the following picture so that you can practice on your own.

## Conclusion

In the end, you will learn all the possible ways of how to highlight partial text in an excel cell by going through this article. If you have any questions or ideas or feedback, please share them in the comment box. This will help me enrich my upcoming articles.

**Related Articles**

**How to Use VLOOKUP to Find Approximate Match for Text in Excel****Fuzzy Lookup in Excel (With Add-In & Power Query)****Excel SUMIF with Partial Match (3 Ways)****VLOOKUP to Find the Closest Match (with 5 Examples)****How to Use Partial VLOOKUP in Excel(3 or More Ways)****Lookup Partial Text Match in Excel (5 Methods)**