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

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.

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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• After that, click on Format.

• 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