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

Question

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,

Chimpanzee

That's because it's not blank. It has a formula in it. Try this =C1="" or if you want to get really crazy =IF(OR(C1="",ISBLANK(C1)),TRUE,FALSE) – Dicta

@Dicta No need to get that crazy because OR(C1="",ISBLANK(C1)) is equivalent. – Blaise

Here is a way to get a true empty blank if condition is met: https://mcmap.net/q/108761/-return-empty-cell-from-formula-in-excel – Ruiz

@Museful, they aren't strictly equivalent. C1="" will work for both scenarios, but ISBLANK(C1) will return false if C1 contains a "" from an IF formula. Your response made it sound like you could use either one in any situation. – Vagrancy

Answer 1

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"))

Televise

Well, the problem for me is not the results of the "blank test" per say, but rather the following: I apply the if statement to a whole row, and then I would like to use Go To -> Special -> Blanks to delete the blank cells from the row, i.e. the cells for which the condition was false. However, the Go to doesn't detect any blank cells in my row, specifically due to this problem that my IF statement doesn't return blank cells per-say. Therefore, I need to alter my IF statement so that it appropriately returns blank cells. – Chimpanzee

If your going to delete them anyways, can you return a different value (i.e. 'deleteme') and then search for that value in your other routine. – Televise

Thanks for the tip! That is precisely what I did! I tried = IF(A1,B1, NA()), and I used Go To -> Special -> Formula -> Errors and I successfully deleted the cells that don't satisfy the condition! Thanks for the tip again! – Chimpanzee

Answer 2

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.

Pestalozzi

The =COUNTBLANK(C1)>0 really does the trick! the ISBLANK() wasn't working for me. thks! – Lactobacillus

Any number > 0 evaluates to TRUE in the context of an IF statement, so you can just substitute IF(COUNTBLANK(C1), .... ) no need for the >0, just to make things cleaner. – Rustie

Here is a way to get a true empty blank as a formula result: https://mcmap.net/q/108761/-return-empty-cell-from-formula-in-excel – Ruiz

Use #N/A in cells instead of leaving them blank – Splenitis

Answer 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.

Paola

Answer 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

Bop

Answer 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.

Quadriceps

Works in libreoffice calc as well! – Mir

Answer 6

I've found this workaround seems to do the trick:

Modify your original formula:

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

Then select the column, search and replace "filler" with nothing. The cells you want to be blank/empty are actually empty and if you test with "ISBLANK" it will return TRUE. Not the most elegant, but it's quick and it works.

Blighter

This won't work, the find and replace will replace filler in the formula leaving the same problems as before. – Crinkle

I used the formula for the filler value, then pasted special to another column with values only, then did the replace with nothing. That finally got what I needed, where I was able to Ctrl+Arrow to jump to the next blank. – Platus

Answer 7

The easiest solution is to use conditional formatting if the IF Statement comes back false to change the font of the results cell to whatever color background is. Yes, technically the cell isn't blank, but you won't be able to see it's contents.

Duprey

It is possible to blank a cell with an if statement. While your solution is feasible it is not the best approach. – Tat

Answer 8

This shall work (modification on above, workaround, not formula)

Modify your original formula: =IF(A1=1,B1,"filler")

Put filter on spreadsheet, choose only "filler" in column B, highlight all the cells with "filler" in them, hit delete, remove filter

Sweatband

I somehow feel that it's not what OP wanted. It seems he wanted a formula. You say that this is not a formula, but still. – Pernod

Answer 9

You can do something like this to show blank space:

=IF(AND((E2-D2)>0)=TRUE,E2-D2," ")

Inside if before first comma is condition then result and return value if true and last in value as blank if condition is false

Olericulture

Answer 10

The formula in C1

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

is either giving an answer of "" (which isn't treated as blank) or the contents of B1.

If you want the formula in D1 to show TRUE if C1 is "" and FALSE if C1 has something else in then use the formula

=IF(C2="",TRUE,FALSE)

instead of ISBLANK

Crinkle

Answer 11

Here is what I do

=IF(OR(ISBLANK(AH38),AH38=""),"",IF(AI38=0,0,AH38/AI38))

Use the OR condition OR(ISBLANK(cell), cell="")

Nash

Answer 12

I think all you need to do is to set the value of NOT TRUE condition to make it show any error then you filter the errors with IFNA().

Here is what your formula should look like =ifna(IF(A1=1,B1,NA()))

Here is a sheet that returns blanks from if condition : https://docs.google.com/spreadsheets/d/15kWd7oPWQmGgYD_PLz9YpIldwnKWoXPHtHQAT3ulqVc/edit?usp=sharing

Nope ... that only works for Googlesheets ... not Excel.

Stony

Answer 13

I found a solution returning N/A and using IFNA() to filter the cell output. In your example, it will be something like:

=IFNA(IF(A1=1,B1,NA()))

Shorttempered

Answer 14

To Validate data in column A for Blanks

Step 1: Step 1: B1=isblank(A1)

Step 2: Drag the formula for the entire column say B1:B100; This returns Ture or False from B1 to B100 depending on the data in column A

Step 3: CTRL+A (Selct all), CTRL+C (Copy All) , CRTL+V (Paste all as values)

Step4: Ctrl+F ; Find and replace function Find "False", Replace "leave this blank field" ; Find and Replace ALL

There you go Dude!

Hereafter

Answer 15

Instead of using "", use 0. Then use conditional formating to color 0 to the backgrounds color, so that it appears blank.

Since blank cells and 0 will have the same behavior in most situations, this may solve the issue.

Zacatecas

I don't think this is a satisfactory answer, as there will now be 0's in the raw spreadsheet data. Exporting, copy/pasting, or any other task that deals with the data will not be correct with all those 0's. – Mcvay

it is bad idea because 0 has trouble with filtering by numbers. other way, 0 can be usual and correct value. then how to recognize between good 0 and "blank" 0 ? I suggest you remove this answer :) – Hopper

Answer 16

This should should work: =IF(A1=1, B1)

The 3rd argument stating the value of the cell if the condition is not met is optional.

Womanhood

Excel returns the value of first argument (FALSE) when condition evaluates to false and third argument does not exist. Unfortunately that does not seem to work. – Gigi

Was sure I'd tested it - but it doesn't replicate. So you're right this answer is totally wrong. Is it best if I delete it to save people's time? – Womanhood