HCR – Text That Contains
Highlight Cell Rules - Text That Contains
With Excel’s preset conditional formatting tool, Highlight Cell Rules, you can alter a range of cells’ appearances according to your own criteria.
One of the alternatives for the criteria is Text That Contains…
The Highlight Cell Rules section of the conditional formatting menu looks like this:
Highlight Cell Rule - Text That Contains Example (with Text)
A cell will be highlighted with one of the look possibilities by the “Text That Contains…” Highlight Cell Rule if a portion of the cell value contains your defined value.
Although a number value can also be used, text is usually the provided value.
“Pidge” is the designated value in this example.
Any range can be selected as the one to which the Highlight Cell Rule should be applied. It could consist of a few cells, one row, one column, or a mix of several rows, columns, and cells.
Let’s use the Name values to implement the rule.
“Text That Contains…” Emphasize Cell Rule, sequentially:
- For the Name values, choose the range A2:A8.
2. From the Home menu, select the Conditional Formatting icon 
in the ribbon.
3. Choose Highlight Cell Rules from the option that drops down.
4. Click on Equal To… in the menu.
This will cause a dialog box to popup, allowing you to choose the appearance option and value.
5. Fill up the input field with Pidge.
6. From the dropdown menu, choose “Yellow Fill with Dark Yellow Text” as the appearance choice.
Now, the cells with values Text That Contains “Pidge” will be highlighted in yellow:
All of these cells are highlighted because the names “Pidgey,” “Pidgeot,” and “Pidgeotto” all begin with “Pidge.”
Note: Any portion of the cell values can be used with the Text That Contains rule.
similar to the following example:
Highlight Cell Rule - Text That Contains Example 2 (with Text)
A cell will be highlighted with one of the look possibilities by the “Text That Contains…” Highlight Cell Rule if a portion of the cell value contains your defined value.
Although a number value can also be used, text is usually the provided value.
“row” will be the supplied value in this case.
Any range can be selected as the one to which the Highlight Cell Rule should be applied. It could consist of a few cells, one row, one column, or a mix of several rows, columns, and cells.
Let’s use the Name values to implement the rule.
“Text That Contains…” Emphasize Cell Rule, sequentially:
- For the Name values, choose the range A2:A8.
2. From the Home menu, select the Conditional Formatting icon in the ribbon.
3. Choose Highlight Cell Rules from the option that drops down.
4. Click on Equal To… in the menu.
This will cause a dialog box to popup, allowing you to choose the appearance option and value.
5. Row should be entered into the input field.
6. From the dropdown menu, choose “Yellow Fill with Dark Yellow Text” as the appearance choice.
Now, the cells with values Text That Contains “row” will be highlighted in yellow:
Since “row” appears at the end of both “Spearow” and “Fearow” names, both cells are highlighted.
Note: Manage Rules allows you to remove the Highlight Cell Rules.
Highlight Cell Rule - Text That Contains Example (with Numbers)
Numbers can also be used with the “Text That Contains…” Highlight Cell Rule.
“7” will be the supplied text value in this example.
Any range can be selected as the one to which the Highlight Cell Rule should be applied. It could consist of a few cells, one row, one column, or a mix of several rows, columns, and cells.
Now let’s apply the rule to every possible stat value.
“Text That Contains…” Emphasize Cell Rule, sequentially:
- For all of the stat values, choose the range C2:H8.
2. Click on the Conditional Formatting icon in the ribbon, from Home menu
3. Select Highlight Cell Rules from the drop-down menu
4. Select Text That Contains… from the menu
This will cause a dialog box to popup, allowing you to choose the appearance option and value.
5. Put 7 in the input field.
6. From the dropdown menu, choose “Green Fill with Dark Green Text” as the appearance choice.
Now, the cells with values That Contains “7” anywhere will be highlighted in green:
Note: Cells with values that match the supplied value in any portion will be highlighted by the Text That Contains… Highlight Cell Rule.
The Equal To… rule can be used to highlight cells that precisely match the given value.
