Conditional formatting a cell containing "<"

[f8]

Folks,

yet another conditional formatting question:

I've got bit sheets with numbers, but as they are analytical results, there
are no "0" values, but these are displayed with a leading "<", for example
"<0.0002".

I want to format this with conditional formatting. comparing every cell in a
row with the content of a single cell at the end of that specific row. (Say,
mark all numbers > $Z$2, with "10" in $Z$2) The problem is that cell content
with that leading "<" is treated as text and not as a small number and
therefore is erroneously formatted when using the above conditional
formatting method.

Any idea how to get this sorted without changing anything in the
spreadsheet? I need the "<" to stay in place.

Cheers!

 

[T. Valko]

Try something like this:

Conditional Formatting
Formula Is:

=--SUBSTITUTE(A2,"<","")>$Z2

 

[Gord Dibben]

Custom Format the cells rather than precede with a typed "<"

< .0000


Gord Dibben MS Excel MVP

 

[B. R.Ramachandran]

Hi,

Assuming that all the rows have the same length (for instance, they all end
at column Z),

Use the following formula for CF in Row 2.
=--MID(A2,2,99)>$Z2
and extend the CF to other columns and rows.

With regards,
B. R. Ramachandran

 

[f8]

Hi B.R. and thanks for your help!

Alas, this did not work for the following reason:

All is good if the cell content is text. But if the cell content is a
number, "20" for example, your formula will return "0" which is not desirable
for me.

However, the following solution worked for me. I simply set"<x.xxx" "0" and
compare with $Z2:

=(IF(LEFT(B2)="<",0,B2))>$Z2

Cheers