CODEWITHSUNDEEP
excel
Charlie
Charlie

Reputation: 37

Contains a string in a cell

I'm trying to do conditional highlighting in Excel that would highlight the entire row of a cell if the cell contains a string.

Say I would like to determine if the cell contains the word "done". The cell can contain such text and would still be able highlight the cell:

  1. done
  2. It is done
  3. abcdone

Upvotes: 1

Views: 865

Answers (1)

adrivent kze
adrivent kze

Reputation: 51

Here's my example data set:

enter image description here

The conditional formatting window looks like this:

The rule applies to =$A$2:$D$13 (entire data table except for the header row) The base formula used is =ISNUMBER(SEARCH(substring,text))

Don't forget to make an absolute reference to the cell containing the search word (substring). For the text, make sure the column has an absolute reference starting in the top-left corner of first data entry, and rows have a relative reference (this is already done in the formula shown above, but I'm just elaborating on it here in general in case you do something similar in the future)

Upvotes: 1

Related Questions