Two types of blank in Excel

Copper Contributor

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?

 

 

9 Replies
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.

 

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.

 

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().

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?

@Joe User 

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