Forum Discussion
Two types of blank in Excel
I have encountered two types of blank in Excel. The first, when you do =(the blank cell) in another cell, gives 0; the second gives blank.
The first, when you do =(the blank cell)+1 gives 1; the second gives #VALUE!
I have been unable to discover what the difference between the two sorts of blank is, or how to turn one into the other short of Copy and Paste. Both show as General under Format Cells, though =TYPE(the blank cell) gives 1 for the first example (number) and 2 for the second (text).
But both seem quite impervious to having any other TYPE value set for them with Format Cells, or via the use of Format Painter.
What am I looking at here?
- One explanation would be that one cell has a space in it (or multiple spaces), so appears blank but a space is treated as text. Try =LEN( blank cell)
Thanks Wyn, but both are empty, and I get 0 for both my sample cells on a LEN test.
Then it is a text string with zero length.
Press DEL to remove the text.
- THis is what I have found too but not sure how to create at will! any help how create a blanke type 1 cell?
Harry_mistry wrote: ``any help how create a blanke type 1 cell? ``
In Excel terms, there are empty cells. I believe that is referred to here as "type 1".
And there are cells that appear to be empty, but they are not. I believe that is referred to here as "type 2".
Note that Excel uses the term BLANK inconsistently. For example, ISBLANK returns TRUE only for empty cells. But COUNTBLANK counts cells that contain the null string and might appear to be empty, but only the null string, as well as truly empty cells.
-----
A truly empty cell is one that has no value and no formula. Consequently, the ISBLANK function returns TRUE.
Also, both ISNUMBER and ISTEXT return FALSE. LEN returns zero. And referencing the cell returns zero or the null string, depending on context.
All cells in a new workbook are empty.
And we can "create" an empty by selecting the cell and pressing Delete or click Clear > Clear All or Clear Contents.
In Excel, there is no formula that returns an "empty" cell, because by definition, the cell has a value, namely the formula. (That might change in more recent or future versions of Excel.)
In contrast, I believe that Google Sheets has function that allows ISBLANK to return TRUE.
-----
A cell that appears to be empty, but it is not, actually does have a value. Consequently, the ISBLANK function returns FALSE.
ISTEXT returns TRUE, but ISNUMBER returns FALSE.
And any reference to the cell returns the cell value as text. If the cell reference is in an arithmetic expression, it causes a #VALUE error because Excel does not interpret the "blank" value as a number.
The "blank" cell value might be the null string, which is written "". In that case, LEN returns zero.
Usually, we create the null string with a formula. For example, ="" or =IF(TRUE,"").
Or we can copy a cell whose value is the null string, then use paste-value into the same or different cell. That makes the cell appear to be truly empty because there is no formula, but it is not.
Alternatively, the cell value might be a space (ASCII 32), a non-breaking space (ASCII 160), or any non-printing character (e.g. tab, which is ASCII 9). In these cases, LEN returns a non-zero value.
Of course, we can create such cell values by manually typing the characters, as well as with a formula.
Is that clear?
Hi Joe,
Its good that you know all about the current situation! thank you for the run down of Null values.
I have read a number of post skirting around the problem .. and many people would feel that this is the only real answer unless some developer in Microsoft is taken to task about failing to implement a NULL variable to allow a formula to decide the NULL ... and not just be left with the pooor options you just described for creating a NULL data TYPE()=1
- To add to the others' answers:
• Can also differentiate between the type of blank cells with =CELL("type",BlankCell) → 3 possible outputs: "b" (for truly blank), "l" (for label - a text string, even if empty), and "v" (for all other values - don't think this one can be blank).
• How to create each:
┣ For truly blank cell: Press Delete.
┗ For empty string: Use formula ="" → then copy that cell and paste it as a value.
