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

## How to Highlight Partial Text in Excel Cell: 9 Ways

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

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

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

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

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

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

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

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

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

**Download Practice Workbook**

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

**<< Go Back to Partial Match Excel | Formula List | Learn Excel**