Two types of blank in Excel

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?