IF statement: how to leave cell blank if condition is false ("" does not work)
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"))
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.
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.