CODEWITHSUNDEEP
excelexcel-2016
Maltesh
Maltesh

Reputation: 413

ISBLANK returns False for empty cells having Formulas

In Excel 2016, a cell with formula which is not yet calculated appears blank. But still ISBLANK returns False.

I am trying to find a way to return True for the cell having formula which is not yet calculated and is actually Blank.

Can someone help me please.

Thanks in Advance!

Upvotes: 11

Views: 50824

Answers (6)

CarolinR
CarolinR

Reputation: 1

I had the same issue the other day. I had to get rid of the "empty" rows (not blank, because they contained a formula) in a range. My solution was to colour them with Conditional Formatting (the rule was searching for ""), then filter the column for the coloured cells, then "Go to special" -> "Visible cells only", press "Delete" and then "Entire row" :-) Cheers, Carolin

Upvotes: 0

Alex
Alex

Reputation: 1

Difficult yes.
My workaround was to show zero values in the sheet (ugly unfortunately)
And then do

=IF(targetcell=0, do nothing, do the thing I want)

Because my targetcell references a list on another sheet. If that list entry is blank, show a 0 and don't do the formula.

Hope helps, Alex

Upvotes: 0

Savrige
Savrige

Reputation: 3755

There are cases where a cell can possible contains one or more whitespaces, causing LEN() to fail. Use trim() combined with len() to overcome this issue, IF whitespace can be considered as a blank cell to you:

=IF(LEN(TRIM(A2))=0,TRUE,FALSE)

Upvotes: 3

karu
karu

Reputation: 1

Use

=IF(A1="",""[VALUE IF TRUE],[VALUE OR A FUNCTION WHEN CONDITION IS FALSE])

instead of isblank because sometimes it assumes a zero matrix that is there in cell A1.

Upvotes: 0

Ben
Ben

Reputation: 331

In case anyone else is having trouble with this, I found a solution.

If a formula returns an empty string, you can't use ISBLANK() because the presence of the formula makes the cell not functionally blank, even if it is visually blank.

But, you can use IF() and LEN() to test how many characters are in the cell. LEN() is a function that counts the number of characters present in a cell. A visually blank cell will have no characters to count. 

=IF(LEN(A1)=0, "This cell is blank", "This cell contains characters")

Upvotes: 33

Justyna MK
Justyna MK

Reputation: 3563

As per previous comments, ISBLANK will return FALSE even if your formula in cell C1 returns an empty string (""). ISBLANK will return TRUE only when a given cell is "truly" blank, i.e. does not contain any formulas or values.

As an alternative, try the following functions:

=C1="" <- will return TRUE, assuming that formula in C1 returns an empty string ("")

=OR(ISBLANK(C1),C1="") <- both formulas combined; will also work in case you remove your original formula from cell C1

=NOT(ISNUMBER(C1)) <- will return TRUE in case the result of your numeric formula is empty (e.g. =IF(LEN(A1),A1+B1,""))

One more formula that you may find useful:

=ISFORMULA(C1)

Hope it helps.

Upvotes: 9

Related Questions