Biff wrote...
There is a huge difference between an empty cell and a blank cell.
So true.
The "problem" arises when people use the 2 terms interchangeably. An empty
cell is one that contains nothing, nothing at all. A blank cell can contain
a formula that returns "nothing" so the cell is not empty, it contains a
formula but the cell appears to be empty.
So wrong!
If X99 contained ="", does ISBLANK(X99) return TRUE or FALSE? If it
returns FALSE, why would you claim 'blank' means cells evaluating to
""?
In worksheet formulas, 'blank' has a precise meaning implied by the
ISBLANK function: the cell contains nothing. 'Empty' has no specified
meaning, so it's more naturally suited for use as meaning cells
containing zero length strings. Then there's the issue of cells
containing strings of nothing but one or more ASCII or HTML nonbreaking
spaces, which would also appear not to contain anything.
Try this:
Enter this FORMULA in A1: =""
And clear cell B1.
Try these formulas:
=ISBLANK(A1)
Indeed, do try this one. Then try
=ISBLANK(B1)
This is where Microsoft screwed up. It'll return 1, but so will
=COUNTBLANK(B1)
=COUNTA(A1)
=LEN(A1)
2 of those formulas might lead you to believe that cell A1 is empty and the
other 2 "indicate" that something's there.
ISBLANK and COUNTA are the reliable indicators. LEN is just doing it's
job. Since Excel converts blank cells (cells containing nothing) to ""
in string contexts, LEN correctly returns 0 when passed a reference to
a blank cell. It's COUNTBLANK that's flawed.
Definitions are everything sometimes. What do you mean by 'empty'?
Also, just because C uses ASCII NUL as a string terminator, so zero
length strings in C would be represented in memory as a single NUL
byte, doesn't mean Excel or any other software or programming language
follows that approach. Indeed, Perl and gawk are two examples of
programming languages closely related to C that don't use ASCIIZ
strings. Strings can be implemented as structures or classes containing
at minimum a length property and a pointer to allocated memory used to
store the string. Such strings could store anything, including several
NUL bytes.
There are 4 formulas here, so in B1:E1?
....
That the 5th and 6th rows are the same as the 1st after clearing A5 and
A6 should be no surprise. Clearing A5 and A6 makes those cells blank,
so they're the same as A1.
As for your 4th row, is the cell formatted as Text? Or are you
forgetting to mention an initial single quote? The numeric constant 0
should produce the same results as the formula =0, so B4:E4 should be
the same as B2:E2 rather than B3:E3.