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

Get FREE Advanced Excel Exercises with Solutions!

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


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.

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.


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.


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.


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.


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


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.


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.


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.


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo