Highlight cells that begin with

To highlight cells that begin with certain text, you can use a formula based on the SEARCH function to trigger a conditional formatting rule. In the example shown, the formula used to highlight values that begin with "mi" is: =SEARCH($G$2,B4)=1 Here, G2 contains the text to search for, and B4 is the cell being evaluated. When this formula returns TRUE, it triggers a conditional formatting rule that applies a bright fill color to cells that begin with "mi", as seen in the worksheet above. The value in cell G2 can be changed at any time. Note: Excel contains many built-in "presets" for highlighting values with conditional formatting, including a preset to highlight cells that begin with specific text. However, defining a rule based on your own formula provides more flexibility and power. For example, you can easily adapt the formula to perform a case-sensitive match, as explained below.

April 14, 2025 · 1 min · 146 words · Kelly Herrera

The highlighting is done automatically with a conditional formatting rule applied to the range B4:G12.

The rule bang out is “Use a formula to determine which cells to format”.

Each cell in B4:G12 is evaluated separately.

Excel formula: Highlight cells that contain

Since B4 is entered as arelative reference, it will change to the cell being evaluated.

Since cell G2 is entered as anabsolute reference($G$2), it will not change.

The formula uses theSEARCH functionto match cells that begin with “mi”.

Excel formula: Highlight cells that end with

SEARCH returns a number that indicates a position when the text is found, and a #VALUE!

error if the text is not found.

The formula returns TRUE when the position is 1 and FALSE for any other value.

Excel COUNTIF function

The value in cell G2 can be changed at any time and the conditional formatting will instantly update.

However, FIND is automatically case-sensitive.

When the text is not found, FIND returns a #VALUE error.

Excel FIND function