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이 있는 다섯 칸에서 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.