Forum Discussion

Roy Brown's avatar
Roy Brown
Copper Contributor
Nov 01, 2018

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 ...
Excel
Roy Brown's avatar
Roy Brown
Copper Contributor

Thanks Wyn, but both are empty, and I get 0 for both my sample cells on a LEN test.

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Nov 01, 2018

Then it is a text string with zero length.

Press DEL to remove the text.

 

  • Roy Brown's avatar
    Roy Brown
    Copper Contributor
    Nov 03, 2018

    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.

     

    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      Nov 03, 2018

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

Resources