-1

Excel assigns the value 0 to a blank cell (Excel: why the value of a blank cell is zero?). The ISBLANK functions checks the contents.

But: Let's consider A1 is empty. B2 is =A1&"foo". B2 results to "foo" and not to "0foo". why?

daniel
  • 34,281
  • 39
  • 104
  • 158
  • 2
    For numerical purposes, the value is 0. But you are not performing a mathematical equation with that cell, but are using the cell as a string (text). Similar to VBA, the default value of a new Integer/Long data type is `0`. The default value of a new String type is `""` (or `vbNullString`). In your example, you are treating the cell as if it were a string, so the string value of an empty cell is an empty string. – K.Dᴀᴠɪs Aug 27 '20 at 08:11
  • so if a parser evaluates a token it has to know the "outer" context? I am not that deep into compiler development, but that seems strange to me – daniel Aug 27 '20 at 08:22
  • I'm not sure where a parser came into play, but the way _Excel_ evaluates an empty cell depends on the operator you are using. See the difference between `=A1 + A2` and `=A1 & A2`? The first is being evaluated by Excel as a numerical expression - which if either of these cells were blank would be considered a numerical 0, and the latter is being evaluated as a string and blanks are evaluated as an empty string, because that's the __default value__ of an empty cell when using this particular operator. – K.Dᴀᴠɪs Aug 27 '20 at 08:32
  • Let's consider A1 is 0 (typed in). B1 is `="foo"&A1`. It results to "foo0". So not how you say. – daniel Aug 27 '20 at 08:34
  • For what it's worth, I don't like the title of the referenced question you posted in your question. I would much more consider the value of an empty cell to be `Null` instead of `0`. – K.Dᴀᴠɪs Aug 27 '20 at 08:39
  • Excel will make a difference between empty and "=0" only if there is a difference while using it in computation. It will only consider the cell value as 0 if you are referring to its numerical value. Otherwise, it will just consider it as empty, using VBA's default values for empty variables, which explains the `="foo"&A1` just being `"foo" & ""`, since an empty string is by default set to "" – romulax14 Aug 27 '20 at 08:45
  • 1
    So after looking into that other question a bit more, perhaps I am seeing better what you are trying to ask. `=A1` returns 0 because Excel will always evaluate as a numerical expression first. Nothing in that equation infers you are working with a string. Now type a simple `'` in A1. You just turned A1 into a string - even though it is still a "blank" cell. Excel cannot evaluate as a mathematical expression any longer, so it will now evaluate as a string. – K.Dᴀᴠɪs Aug 27 '20 at 08:54
  • ' is not blank. ' looks blank for the user but is not evaluated as true by `ISBLANK` function – daniel Aug 27 '20 at 09:00
  • 1
    Right. The Value went from `Null` to a zero-length string `""`. So it's not blank. Just as if you were to type the `0` you've been all about in this post. 0 is the mathematical equivalent to "Nothing". A zero-length string is the string equivalent to "Nothing". But no where did I say `Nothing = Null`. – K.Dᴀᴠɪs Aug 27 '20 at 09:04

2 Answers2

1

Excel will use the default empty variable value as the value of the empty cell depending on how you are trying to access it.
If you are using it as a numerical value, excel will use the default value for empty Integer, Double, etc., which is 0.
If you are using it as a string, like when you concatenate with ="foo" & A1, it will use String default empty value, which is "". So "foo" & "" is just "foo".

See here for a better explanation.

romulax14
  • 555
  • 2
  • 12
0

Because =ISBLANK() and =0 are not the same thing.

A cell will return true to ISBLANK if it does not have any contents set.

A cell will return 0 if it contains a value of 0, or if it contains a value that evaluates to 0, or if it contains a string that evaluates to 0, or if you try to perform an equation with a blank cell. Equations need numbers, and if an equation is performed on something that is blank then it has the same effect as using 0.

ed2
  • 1,457
  • 1
  • 9
  • 26