Empty cell isn't blank???

[Simon Topping]

Hi,

I have a series of formulae dependant on cells being
blank. Using the 'isblank()' function however isn't
working.

Upon clicking in the cell which is supposed to be blank
and pressing 'delete', I've found that my formula works.

Is there a way to search through a work book for cells
that appear to be blank and then automatically editing
them so that the 'isblank()' formula would work?

Any other suggestions if this is not possible?


Cheers


Simon

 

[Alan]

You could try substituting
=ISBLANK(A1)
with
=A1=""
Regards,
Alan.

 

[Alan]

or
=IF(OR(ISBLANK(A1),A1=""),TRUE,FALSE)

You could try substituting
=ISBLANK(A1)
with
=A1=""
Regards,
Alan.

 

[Paul]

Hi,

I have a series of formulae dependant on cells being
blank. Using the 'isblank()' function however isn't
working.

Upon clicking in the cell which is supposed to be blank
and pressing 'delete', I've found that my formula works.

Is there a way to search through a work book for cells
that appear to be blank and then automatically editing
them so that the 'isblank()' formula would work?

Any other suggestions if this is not possible?

There is a problem in searching for and automatically clearing cells that
'appear' to be blank. They could appear blank for many reasons, including
quite legitimate things such as containing a formula returning "".

You could use
Edit > Go To > Special > Blanks
to highlight all truly blank cells.
You could then look for cells that you know should be blank but which aren't
highlighted, so you know which to clear.

 

[Dave Peterson]

Any chance you (or your users) hit the spacebar to "clear" a cell?

If no, then I'm betting you had a formula that evaluated to "" and you did a
copy|pastespecial Values.

If you did this, you can see what's left over by using:
Tools|options|Transition Tab
check Transition Navigation Keys

Look at the cell -- still looks empty.

But look at the formulabar and you'll see an apostrophe (') all by its lonesome.

One way to change those apostrophes to really empty cells:

Select your range (ctrl-A) for the whole sheet
edit|replace
Find what: (leave empty)
Replace with: $$$$$$ (some unique unused string)
Replace All

Then do it again to reverse it:
edit|replace
Find what: $$$$$$ (some unique unused string)
Replace with: (leave empty)
Replace All