8

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!

Maltesh
  • 383
  • 2
  • 6
  • 14
  • A blank cell contains nothing -- not even a formula with a result of nothing. Try `A1=""` – cybernetic.nomad Mar 21 '19 at 18:23
  • Nope. Not possible. Lets say, I have a cell C1 and it has formula: A1+B1. When I have not entered any values in A1 & B1, C1 will be empty. But still C1 returns False for ISBLANK. How can I check if C1 is actually Blank? – Maltesh Mar 21 '19 at 18:34
  • 1
    In your example C1 = 0 so is not blank or empty. – SJR Mar 21 '19 at 18:38
  • To echo the previous comment, in your example `C1` actually is calculated and equals 0. So this is not an example of "a cell with a formula which is not yet calculated." What is the actual formula you are using? – BigBen Mar 21 '19 at 19:03
  • I am using =(ISBLANK(C1). It is returning False inspite of the cell is Blank. It doesnot make sense when the C1 does not contain any value (except formula). How can I check if cell only contains formula and not value? – Maltesh Mar 22 '19 at 03:09

6 Answers6

21

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")
Community
  • 1
  • 1
Ben
  • 211
  • 3
  • 3
6

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.

Justyna MK
  • 3,523
  • 3
  • 11
  • 25
1

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)
Savrige
  • 3,352
  • 3
  • 32
  • 38
0

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.

m4n0
  • 29,823
  • 27
  • 76
  • 89
karu
  • 1
0

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

Tyler2P
  • 2,324
  • 26
  • 22
  • 31
Alex
  • 1
0

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

CarolinR
  • 1