How to convince Excel a cell is "blank"

  • Thread starter Thread starter DaMoose
  • Start date Start date
D

DaMoose

I have some forumulas that say if a reference cell is not
equal to a certain number, then "", otherwise do
something else. Well, when I copy these cells (the cells
that are "") and paste special values (using the skip
blanks option), the paste result does not skip these
cells because it does not consider them "blank". What
can I do to make these cells qualify as "blank" in the
world of Excel, while still keeping my formula in that
cell?

Thanks
 
You can't. A cell with a formula is automatically not Blank.

In a worksheet function you can test with something like
=IF(TRIM(A1)="",True,False)
or if you want to make sure the length is zero
=IF(LEN(A1)=0, True, False)

You can use the VBA functions of the same name in a macro
to test individual cells before deciding what you want to do.

You could make a copy of the worksheet and then test with
TRIM to wipe out anything that looks empty.
 
Depends what the formula returns? Before copying, try selecting the range,
then Edit -- Go To -- Special. Formulas (Numbers) might be a viable option.

HTH,
Andy
 
Ok.. thanks for the help.

One thing that still surprises me is that when I paste
special - values a group of cells that my initial formula
has deemed "", Excel still does not see them as blank.
 
Did you miss my first sentence. To which I'd also add
a cell with the prefix single quote to indicate a text constant
with nothing else after it. Are also not empty or blank.
 
A blank cell and one containing a zero-length text string are not the same thing. The latter
isn't blank. It contains text, even though you don't see anything.
 
Thanks for this "NEEDED" clarification.

Myrna Larson said:
A blank cell and one containing a zero-length text string are not the same thing. The latter
isn't blank. It contains text, even though you don't see anything.
Click to expand...
 
Have you tried clearing the apparently blank cells?
Right click on 'dodgy' cell and use the 'clear contents '
option


Gren
 
After you've done Paste Special Values, select the range and run this 1-line macro:

Selection.Value = Selection.Value

This will make the cells with the "" in them truly blank.

Regards,
Paul


JMay said:
Thanks for this "NEEDED" clarification.
Click to expand...
 
Back
Top