ISBLANK と空文字列の違い
一度も入力していないセル Z99 では ISBLANK が TRUE、空文字列との比較も TRUE、LEN は 0、ISTEXT は FALSE。数式が空文字を返しても空に見えるが、ISBLANK は FALSE、ISTEXT は TRUE、LEN は 0 で、空文字列との比較は TRUE のまま。Microsoft の IS 関数の説明では、空セルへの参照のときだけ ISBLANK は TRUE になる。
要点
一度も入力していないセル Z99 では ISBLANK が TRUE、空文字列との比較も TRUE、LEN は 0、ISTEXT は FALSE。数式が空文字を返しても空に見えるが、ISBLANK は FALSE、ISTEXT は TRUE、LEN は 0 で、空文字列との比較は TRUE のまま。Microsoft の IS 関数の説明では、空セルへの参照のときだけ ISBLANK は TRUE になる。
=COUNTBLANK(B2:B6) → 2
=COUNTA(B2:B6) → 5確認のポイント
スペースも数値 0 も、どちらの判定でも空ではない。スペースの LEN は 1、ISTEXT は TRUE。0 の LEN は 1、ISTEXT は FALSE。空文字との比較は未入力セルと空文字を返す数式を同じものとして扱う。ISBLANK は前者だけ。Microsoft の「IF で空白を調べる」記事も両方の形を示している。
注意点と互換性
空文字列が 2 つ、スペース、文字列 001、数値 0 を含む 5 セルでは、COUNTBLANK は 2、COUNTA は 5。Z99 だけを見ると COUNTBLANK は 1、COUNTA は 0。Microsoft によれば COUNTBLANK は空文字を返す数式を数え、0 は数えない。COUNTA は空文字を数え、本当に空のセルは数えない。このシートと同じ。API で空文字を書き込むと ISBLANK は FALSE のままで、空文字を返す数式と同じだった。本当の空白は、未入力の Z99 で確認した。
英語の詳細例・数式・著者メモ
Excel ComparisonA cell can look empty and still fail ISBLANK. I have wasted time on IF(ISBLANK(A2),"please fill this in",…) only to find A2 held a formula that returned "". It looks the same. It is not the same.
I checked four ordinary values, plus a cell I had never touched (Z99).
| What is in the cell | ISBLANK | = "" | LEN | ISTEXT |
|---|---|---|---|---|
Unused cell (Z99) | TRUE | TRUE | 0 | FALSE |
="" | FALSE | TRUE | 0 | TRUE |
| A single space | FALSE | FALSE | 1 | TRUE |
Text 001 | FALSE | FALSE | 3 | TRUE |
The number 0 | FALSE | FALSE | 1 | FALSE |
The unused cell is the only one ISBLANK accepted. Microsoft’s IS functions page is blunt about this: ISBLANK is TRUE when the argument is a reference to an empty cell, otherwise FALSE. A formula that returns empty text is not an empty cell. In this file ISBLANK of ="" was FALSE, ISTEXT was TRUE, and LEN was 0.
Z99="" was also TRUE. So IF(A2="") treats a never-used cell and a formula-empty cell as the same thing. That is often what I want on a form: “nothing useful is here.” IF(ISBLANK(A2)) is the narrower test: “nobody has written a formula or a value into this cell.” Microsoft’s page on using IF to check for a blank shows both patterns; pick the one that matches the data, not the one whose name sounds right.
A space is not empty under either test. LEN was 1. If a paste put " " into a column, both ISBLANK and ="" will say the cell is occupied. Trim first if that is a real risk.
The number 0 is also occupied. ISBLANK was FALSE, 0="" was FALSE, LEN was 1, and ISTEXT was FALSE. Do not use a blank check to look for zeros.
Counting the two kinds of “empty”
I ran COUNTBLANK and COUNTA on B2:B6, a five-cell mix that included two empty strings (one from ="", one I had stored as empty text), a space, the text 001, and the number 0:
=COUNTBLANK(B2:B6) → 2
=COUNTA(B2:B6) → 5Then the unused cell by itself:
=COUNTBLANK(Z99) → 1
=COUNTA(Z99) → 0
=IF(ISBLANK(Z99),"blank","not") → blank
=IF(Z99="","empty","not") → emptyCOUNTBLANK counted both empty strings in B2:B6, and it did not count the 0. That is exactly what Microsoft’s COUNTBLANK page says: formulas that return "" are counted, zeros are not. COUNTA goes the other way: it counts a formula that returns empty text, and it does not count a truly empty cell. COUNTA(Z99) was 0; COUNTA of the five-cell mix was 5, so the empty strings were included.
If you are trying to count “rows someone still has to fill,” COUNTA will treat a helper formula that returns "" as filled. COUNTBLANK or IF(A2="") will treat it as empty. I would use ISBLANK only when I specifically care that the cell has never been written to.
One practical note from the test itself: writing an empty string into a cell through an API left ISBLANK FALSE, ISTEXT TRUE, and LEN 0 — the same profile as ="". A cell that looks blank in the grid is not always an empty cell. I used Z99, which I had never written to, as the genuine blank.
This is a different problem from a #VALUE! on a function that will not accept a blank, or from IFERROR hiding a miss. Those are in VALUE error and IFERROR vs IFNA. And a stored "" can also show up as its own “unique” value in a list; see Unique Values.