Nov 01 2018 04:31 AM - edited Nov 03 2018 12:57 AM
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?
Nov 01 2018 04:38 AM
Nov 01 2018 06:14 AM
Thanks Wyn, but both are empty, and I get 0 for both my sample cells on a LEN test.
Nov 01 2018 06:43 AM
Then it is a text string with zero length.
Press DEL to remove the text.
Nov 03 2018 01:17 AM
Thanks Detlef, I am sure you are right.
I will post an example shortly with both entries, so it can be looked at.
I have always thought it wrong that =(Type 1 blank cell) gives a zero (on spreadsheets that don’t have zero suppress), but I suspect that there are too many spreadsheets out there that rely on this for Microsoft to ever change this.
But perhaps the issue here is a bug in the Excel Format Cell display, which does not correctly show the format of a zero length text string as Text, but rather shows it as General? I see this both in Excel 2013 and Excel 2016, though TYPE gets it right in both.
Nov 03 2018 02:13 AM
Roy, you should first check for ISBLANK() = TRUE. Then there is nothing in the cell.
If it is FALSE then the content could be a number, a text, a logical value or an error.
All four can also be checked: ISNUMBER(), ISTEXT(), ISLOGICAL(), ISERROR().
Nov 24 2018 01:13 PM
Jun 15 2022 03:09 AM
Jun 15 2022 07:35 AM
@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?
Jun 15 2022 07:49 AM
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