conditional format formula

  • Thread starter Thread starter dzelnio
  • Start date Start date
D

dzelnio

I need a formula that looks says any cell that does not contain a "-",
"0" or blank will now equal the number in column B.

Any takers?

Dave
 
If "0" or "-" or <blank> in D12, then
=IF(OR(D12="-",D12="",D12="0"),B12,"not specified")
There's probably a shorter way of doing this, but it works ok :)
 
Here's one way:
=IF(AND(NOT(ISNUMBER(FIND("-",A1,1))),NOT(ISNUMBER(FIND("0",A1,1))),NOT(ISNUMBER(FIND("
",A1,1)))),B1,"")
 
I need a formula that looks says any cell that does *not* contain a "-",
I think that Jock may have intended:

=IF(OR(D12="-",D12="",D12="0"),"not specified",B12)

or

=IF(AND(D12<>"-",D12<>"",D12<>"0"),B12,"not specified")

note that by puting the zero in quotes it will only match with a text zero.

This assumes that the OP intended that the cell contains *only* one of those
characters and not contains one of the characters within other text.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top