Conditional Formatting - If cell contains a zero?

0
votes

I have column U:

0, 0, 2, 1
0, 0, 0, 0
12
1
0
0,2,1

I am trying to use conditional formatting to highlight the cell if the cell contains a zero.

=SEARCH("substring",U1)="0"

This code doesn't seem to be working right, it also highlights cells not containing zero. I'm not sure if the comma is causing the problem?

Please can someone show me what i'm doing wrong?

1
excelconditional-formatting
Are all the items in the column Text or are some of the items Numbers ?? – Gary's Student
@Gary'sStudent my cells are formatted general but the column only contains numbers – user7415328

1 Answers

1
votes

Change your formula to =ISNUMBER(SEARCH("0",U1)). This will look for the character zero, and highlight the cell if this returns TRUE.

Also, if you drag it down to other cells, Excel will change it to $U$1. You should manually remove this anchoring so that it looks at each cell on its own.