Highlight Cells That Begin (Or End) With In Excel – Excel & Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on July 9, 2022

This tutorial will demonstrate how to highlight cells that begin or end with certain text using Conditional Formatting in Excel and Google Sheets.

 

begin end with master

 

Highlight Cells that Begin With or End With

You can highlight cells where the value in the cell either begins or ends with certain text by creating a Conditional Formatting rule.

  1. Select the range you want to apply formatting to.
  2. In the Ribbon, select Home > Conditional Formatting > New Rule.

 

begin end with menu

 

  1. Select Use a formula to determine which cells to format, and enter the following COUNTIF formula:
=COUNTIF(B4, "La*")

 

begin end with formula

 

This will find out if the text starts with La—.

  1. Alternatively, to find out if the text in a cell ends with certain text, type in the formula:
=COUNTIF(B4, "*ke")

 

begin end with formula end

 

  1. Click on the Format button and select your desired formatting.

 

conditional formatting greater less than format

 

  1. Click OK, then OK again to return to the Conditional Formatting Rules Manager.

 

begin end with rule manager

 

  1. Click Apply to apply the formatting to your selected range and then click Close.

 

begin end with master final

 

Highlight Cells That Begin or End With – Google Sheets

The process to highlight cells where the value in the cell either begins or ends with certain text in Google sheets is like the process in Excel.

  1. Highlight the cells you wish to format, and then click on Format > Conditional Formatting.

 

google sheets begin end with menu

 

  1. The Apply to Range section will already be filled in.

 

google sheets not equal to range

 

  1. From the Format Rules section, select Custom Formula from the drop-down list and type in the following formula to look for text at the beginning of your cell.
=COUNTIF(B4, "La*")

 

google sheets begin end with formula start

 

  1. Alternatively type in this formula to look for text at the end of your cell.
=COUNTIF(B4, "*ke")

 

google sheets begin end with formula end

 

  1. Select the fill style for the cells that meet the criteria.

 

google sheets not equal to format

 

  1. Click Done to apply the rule.

 

google sheets begin end with final

AI Formula Generator

Try for Free

See all How-To Articles