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.

highlight partial text in excel cell


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.

highlight partial text in excel cell

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.

highlight partial text in excel cell

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

highlight partial text in excel cell

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

highlight partial text in excel cell

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.

highlight partial text in excel cell

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

highlight partial text in excel cell

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

highlight partial text in excel cell

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.

highlight partial text in excel cell

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

highlight partial text in excel cell

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

highlight partial text in excel cell

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.

highlight partial text in excel cell

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


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

highlight partial text in excel cell

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

highlight partial text in excel cell

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

highlight partial text in excel cell

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.

highlight partial text in excel cell

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

highlight partial text in excel cell

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: Lookup Partial Text Match in Excel (5 Methods)


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

highlight partial text in excel cell

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

highlight partial text in excel cell

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

highlight partial text in excel cell

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.

highlight partial text in excel cell

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.

highlight partial text in excel cell

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 String in Excel (5 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.

highlight partial text in excel cell

  • 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

highlight partial text in excel cell

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.

highlight partial text in excel cell

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

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo