Meraz Al Nahian
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.
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
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 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 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.
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
- 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 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.
- 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.
