ISBLANK 與空字串的區別
從未寫入過的單元格 Z99，ISBLANK 為 TRUE，和空字串比較也為 TRUE，LEN 為 0，ISTEXT 為 FALSE。公式返回空文本時看起來同樣是空的，但 ISBLANK 為 FALSE，ISTEXT 為 TRUE，LEN 為 0，和空字串比較仍為 TRUE。微軟對 IS 函式的說明是：只有引用空單元格時 ISBLANK 才為 TRUE。
快速解答
從未寫入過的單元格 Z99，ISBLANK 為 TRUE，和空字串比較也為 TRUE，LEN 為 0，ISTEXT 為 FALSE。公式返回空文本時看起來同樣是空的，但 ISBLANK 為 FALSE，ISTEXT 為 TRUE，LEN 為 0，和空字串比較仍為 TRUE。微軟對 IS 函式的說明是：只有引用空單元格時 ISBLANK 才為 TRUE。
=COUNTBLANK(B2:B6) → 2
=COUNTA(B2:B6) → 5檢查重點
空格和數字 0 在兩種判斷下都不是空。空格的 LEN 為 1，ISTEXT 為 TRUE。0 的 LEN 為 1，ISTEXT 為 FALSE。用空字串比較會把從未輸入的單元格和返回空文本的公式當成同一類；ISBLANK 只覆蓋前者。微軟介紹用 IF 檢查空白時也寫了這兩種寫法。
邊界與相容性
對包含兩個空字串、一個空格、文本 001 和數字 0 的五格區域，COUNTBLANK 為 2，COUNTA 為 5。單獨看 Z99，COUNTBLANK 為 1，COUNTA 為 0。微軟說明 COUNTBLANK 會統計返回空文本的公式、但不統計 0；COUNTA 會統計空文本、但不統計真正的空單元格。這和本表一致。通過介面寫入空字串後，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.