Blank, yet not blank

R

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
 
B

bigwheel

Rob said:
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
Click to expand...
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
 
R

Rob

Oops!
I meant it gives the answer as 0.

Rob

bigwheel said:
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
Click to expand...
 
R

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.
 
R

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
 
Top