I have a 120x120 matrix of five digit numbers like so:

52000 42222 43213 01016
52020 42200 43202 01000
52000 42202 43103 01016
52020 42200 43100 01000
52300 42000 43002 01010
52300 42020 43010 01010
53000 40222 41213 01216
53020 40200 41202 01200
53000 40232 41103 01006

....and so forth.

I want to highlight the cells that do not include a zero (the reverse works
as well for me, highlighting the cells that do include a zero). I can't seem
to make an if statement with find or search work out.

Thanks in advance.

Highlight your cells then

Format=Conditional Formatting=Formula is:=NOT(ISNUMBER(FIND(0,A1))) and
set colour as required

Assumes A1 is top left of your range.

HTH

"Rothman" wrote:

I have a 120x120 matrix of five digit numbers like so:

52000 42222 43213 01016
52020 42200 43202 01000
52000 42202 43103 01016
52020 42200 43100 01000
52300 42000 43002 01010
52300 42020 43010 01010
53000 40222 41213 01216
53020 40200 41202 01200
53000 40232 41103 01006

...and so forth.

I want to highlight the cells that do not include a zero (the reverse works
as well for me, highlighting the cells that do include a zero). I can't seem
to make an if statement with find or search work out.

Thanks in advance.

=SEARCH("0",A1)

this will work if you have formatted everything as text, otherwise you will
have to use this formula

=SEARCH("0",TEXT(A1,"00000"))

"Rothman" wrote:

I have a 120x120 matrix of five digit numbers like so:

52000 42222 43213 01016
52020 42200 43202 01000
52000 42202 43103 01016
52020 42200 43100 01000
52300 42000 43002 01010
52300 42020 43010 01010
53000 40222 41213 01216
53020 40200 41202 01200
53000 40232 41103 01006

...and so forth.

I want to highlight the cells that do not include a zero (the reverse works
as well for me, highlighting the cells that do include a zero). I can't seem
to make an if statement with find or search work out.

Thanks in advance.

Using conditional formatting, this formula seemed to do the trick, using
formula is:
=ISNUMBER(SEARCH(0,a1))=FALSE
and then changing the pattern to whatever color you want to use.

As I did nothing special when pasting your numbers, 01216 did not keep the
leading 0 and this number was highlighted also, but you have no doubt already
either used a leading apostrophe or formatted cells as text.
--
Kevin Vaughn


"Rothman" wrote:

I have a 120x120 matrix of five digit numbers like so:

52000 42222 43213 01016
52020 42200 43202 01000
52000 42202 43103 01016
52020 42200 43100 01000
52300 42000 43002 01010
52300 42020 43010 01010
53000 40222 41213 01216
53020 40200 41202 01200
53000 40232 41103 01006

...and so forth.

I want to highlight the cells that do not include a zero (the reverse works
as well for me, highlighting the cells that do include a zero). I can't seem
to make an if statement with find or search work out.

Thanks in advance.

Works like a charm. Thanks!

"Toppers" wrote:

Highlight your cells then

Format=Conditional Formatting=Formula is:=NOT(ISNUMBER(FIND(0,A1))) and
set colour as required

Assumes A1 is top left of your range.

HTH

"Rothman" wrote:

I have a 120x120 matrix of five digit numbers like so:

52000 42222 43213 01016
52020 42200 43202 01000
52000 42202 43103 01016
52020 42200 43100 01000
52300 42000 43002 01010
52300 42020 43010 01010
53000 40222 41213 01216
53020 40200 41202 01200
53000 40232 41103 01006

...and so forth.

I want to highlight the cells that do not include a zero (the reverse works
as well for me, highlighting the cells that do include a zero). I can't seem
to make an if statement with find or search work out.

Thanks in advance.