Conditional Formatting If Cell Contains Specific Text – Excel & Google Sheets

This tutorial will demonstrate how to highlight cells if they contain specific text using Conditional Formatting in Excel and Google Sheets.

 

contains specific text master

 

Highlight Cells With Specific Text

Use a Formula

To highlight cells with certain text defined in another cell, you can use a formula in Conditional Formatting.

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

 

contains specific text menu

 

  1. Select Use a formula to determine which cells to format, and enter the following formula (using the SEARCH and ISNUMBER Functions):

 

contains specific text formula

 

  1. Cell F10 needs to be locked as an absolute reference. You can do this by using the $ sign around the row and column indicators, or by pressing F4 on the keyboard.
  2. 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.

 

contains specific text rule manager

 

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

This formula returns TRUE when a cell contains the text stored in F9 and formats those cells accordingly.

Notes:

Built-in Conditional Formatting Rule

Another option is to use one of the default rules that are in Excel to find if the text you require is in the selected cells.

  1. Select the cells you require and then click on Home > Conditional Formatting > Highlight Cells Rules > Text that Contains

 

contains specific text text that contains rule

 

  1. Select Cell F5 to indicate the text to find, and then select the formatting you wish to apply.

 

contains specific text text that contains dropdown

 

  1. Click OK.

 

contains specific test final

 

Highlight Cells With Specific Text in Google Sheets

Highlighting cells based on their text is similar in Google Sheets.

  1. Highlight the cells you wish to format, then go to Format > Conditional Formatting.

 

google sheets contains specific text menu

 

  1. The Apply to Range section is already filled in.

 

google sheets contains specific text range

 

  1. From the Format Rules section, select Custom formula is and type in the same formula used in Excel above.

 

google sheets contains specific text formula

 

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

 

google sheets conditional formatting greater less than format

 

  1. Click Done to apply the rule.

 

google sheets contains specific text final

 

As with Excel, you can also apply Conditional Formatting by selecting “Text contains” rather than “Custom formula is” from the Format Rules section of the Conditional format rules box.

 

google sheets contains specific text cond formatting