When is an empty cell not really empty?

  • Thread starter Thread starter prlaba
  • Start date Start date
P

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
 
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'
 
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?
 
Back
Top