When is an empty cell not really empty?

[prlaba]

I have encountered the strangest behavior I've ever seen in a workbook.

Basically, I have two cells in a worksheet that are both empty. B
empty I mean that the Len() function applied to each cell returns 0.

But various worksheet IS functions return some unexpected results, a
shown below:

a
Function Cell 1 Cell 2
------------------------------------------------
=LEN(cell) 0 0
=ISBLANK(cell) TRUE FALSE
=ISNUMBER(cell) FALSE FALSE
=ISNONTEXT(cell) TRUE FALSE
=VALUE(cell) 0 #VALUE#
=(cell = 0) TRUE FALSE
=(cell > 0) FALSE TRUE
a

The EXACT() function applied to the two cells returns TRUE, indicatin
that the cells are exactly the same (in spite of the differences show
above).

If I select Cell 1 and delete its contents, nothing changes. But if
select Cell 2 and delete its contents (what contents?!), then all o
the functions return the same results as Cell 1. That suggests tha
Cell 1 is empty, but Cell 2 is not. So what exactly is "in" Cell
that causes it to behave as it does, even though the Len() functio
returns 0?

Can anyone explain what might be going on here?

Thanks

 

[Frank Kabel]

Hi
sound like you have a 'zero-length' string in this cell. You can
reproduce this as dollows:
- insert the formula ="" in any cell
- copy this cell
- insert this cell with 'Edit - Paste Special - Values'

 

[Larry]

I got the same problem when exporting from Access to Excel.
Drove me bonkers that I had to use NOT(ISNUMBER()) rather
than simply ISBLANK(). If I pressed <Enter> in the cell,
it became blank. Ugh

-----Original Message-----
I have encountered the strangest behavior I've ever seen
in a workbook.

Basically, I have two cells in a worksheet that are both
empty. By empty I mean that the Len() function applied to
each cell returns 0.

But various worksheet IS functions return some unexpected
results, as shown below:

Function Cell 1 Cell 2
------------------------------------------------
=LEN(cell) 0 0
=ISBLANK(cell) TRUE FALSE
=ISNUMBER(cell) FALSE FALSE
=ISNONTEXT(cell) TRUE FALSE
=VALUE(cell) 0 #VALUE#
=(cell = 0) TRUE FALSE
=(cell > 0) FALSE TRUE

The EXACT() function applied to the two cells returns
TRUE, indicating >that the cells are exactly the same (in
spite of the differences shown >above).

If I select Cell 1 and delete its contents, nothing
changes. But if I select Cell 2 and delete its contents
(what contents?!), then all of the functions return the
same results as Cell 1. That suggests that Cell 1 is
empty, but Cell 2 is not. So what exactly is "in" Cell 2
that causes it to behave as it does, even though the Len()
function returns 0?