Blank, yet not blank

[Rob]

Hi,

I copied some info from a database and pasted it to Excel where I wanted to
manipulate the data. I notice that some of the cells, though appearing to
be blank (and not showing 0 either), still responded to a simple formula
like IF(OR(A1="",A1=0),1,0) giving the answer as 1.

Can anyone tell me what's happening here, as it does mean I need to
unneccessarily extend the formulas to exclude these cells.

Rob

 

[Guest]

Hi,

I copied some info from a database and pasted it to Excel where I wanted to
manipulate the data. I notice that some of the cells, though appearing to
be blank (and not showing 0 either), still responded to a simple formula
like IF(OR(A1="",A1=0),1,0) giving the answer as 1.

Can anyone tell me what's happening here, as it does mean I need to
unneccessarily extend the formulas to exclude these cells.

Rob

Your formula tests for the cell being blank or containing 0 if either of
these conditions are true you'll get an answer of 1

 

[Rob]

Oops!
I meant it gives the answer as 0.

Rob

Your formula tests for the cell being blank or containing 0 if either of
these conditions are true you'll get an answer of 1

 

[Ragdyer]

They could contain <space>.
Try:
=LEN(A1)
And see if you get a >0 answer.

And if this data originated from the web, that space might very well be a
Char(160), non-breaking space, and not a "normal" Char(32) space.

 

[Rob]

Thanks RD.

The answer is 1 so I assume it's one space, as when I select the cell and
click in the formula bar I can backspace once.

Rob