Returns "1", the specified cell is empty

Question

In Excel, I need to return a value of 1 if the reference cell is empty

I can do this if the value is zero , but how to do it if it is empty?

Answer 1

You can use:

 =IF(ISBLANK(A1),1,0) 

but you have to be careful what you mean by an empty cell. I have been caught this before. If you want to know if the cell is really empty, isblank will work as above. Unfortunately, sometimes you also need to know if it does not contain any useful data.

Expression:

 =IF(ISBLANK(A1),TRUE,(TRIM(A1)="")) 

will return true for cells that are really empty or contain only a space.

Here are the results, when column A contains different numbers of spaces, column B contains length (so you know how many spaces) and column C contains the result of the expression:

 <-A-> <-B-> <-C-> 0 TRUE 1 TRUE 2 TRUE 3 TRUE 4 TRUE 5 TRUE a 1 FALSE <-A-> <-B-> <-C-> 

Return 1 if the cell is empty or a space, and 0 otherwise:

 =IF(ISBLANK(A1),1,if(TRIM(A1)="",1,0)) 

will do the trick.

This trick comes in handy when the cell you are checking is actually the result of an Excel function. Many Excel functions (such as trim) return an empty string, not an empty cell.

You can see it in action with a new sheet. Leave cell A1 as it is and set A2 to =trim(a1) .

Then set B1 to =isblank(a1) and B2 to isblank(a2) . You will see that the former is true, and the latter is untrue.

Answer 2

P4 is cell check i for:

 =IF(ISBLANK(P4),1,0) 

Answer 3

Paxdiablo's answer is absolutely correct.

In order not to write the return value of 1 twice, I would use this instead:

 =IF(OR(ISBLANK(A1),TRIM(A1)=""),1,0) 

Answer 4

 =if(a1="","1","0") 

In this formula, if the cell is empty, then the result will be 1 otherwise it will be 0

Answer 5

Compare the cell with "" (empty line):

 =IF(A1="",1,0) 

Answer 6

If you have a cell filled with spaces or spaces, you can use:

 =Len(Trim(A2)) = 0 

if the cell you tested was A2

Answer 7

You may need to use =IF(ISNUMBER(A1),A1,1) in some situations when you are looking for numeric values ​​in a cell.

Answer 8

Since it is required quite often, this may be brief:

 =1*(A1="") 

This will not return 1 if the cell is empty, but contains a space or formula of the form =IF(B1=3,"Yes","") , where B1 does not contain 3 .

=A1="" will return either TRUE or FALSE , but those in the equation are treated as 1 and 0 respectively, so multiplying TRUE by 1 returns 1 .

The same can be done with double unary -- :

 =--(A1="") 

where, when A1 is empty, minus negates TRUE to -1, and the other negates that for 1 (only + instead of -- , however, does not change TRUE to 1 ).