IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example:

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

and if C1 is tested for being blank or not using =ISBLANK(C1), this would return FALSE, even if C1 seems to be blank. This means that the =IF(A1=1,B1,"") formula does not technically leave the cells blank if the condition is not met.

Any thoughts as to a way of achieving that? Thanks,

Solution 1:

Unfortunately, there is no formula way to result in a truly blank cell, "" is the best formulas can offer.

I dislike ISBLANK because it will not see cells that only have "" as blanks. Instead I prefer COUNTBLANK, which will count "" as blank, so basically =COUNTBLANK(C1)>0 means that C1 is blank or has "".

If you need to remove blank cells in a column, I would recommend filtering on the column for blanks, then selecting the resulting cells and pressing Del. After which you can remove the filter.

Solution 2:

Try this instead

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

This will return true for cells that are either truly blank, or contain nothing but white space.

See this post for a few other options.

edit

To reflect the comments and what you ended up doing: Instead of evaluating to "" enter another value such as 'deleteme' and then search for 'deleteme' instead of blanks.

=IF(ISBLANK(C1),TRUE,(TRIM(C1)="deleteme"))

Solution 3:

I wanted to add that there is another possibility - to use the function na().

e.g. =if(a2 = 5,"good",na());

This will fill the cell with #N/A and if you chart the column, the data won't be graphed. I know it isn't "blank" as such, but it's another possibility if you have blank strings in your data and "" is a valid option.

Also, count(a:a) will not count cells which have been set to n/a by doing this.

Solution 4:

If you want to use a phenomenical (with a formula in it) blank cell to make an arithmetic/mathematical operation, all you have to do is use this formula:

=N(C1)

assuming C1 is a "blank" cell

Solution 5:

You could try this.

=IF(A1=1,B1,TRIM(" "))

If you put this formula in cell C1, then you could test if this cell is blank in another cells

=ISBLANK(C1)

You should see TRUE. I've tried on Microsoft Excel 2013. Hope this helps.