How to convince Excel a cell is "blank"

[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

 

[David McRitchie]

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.

 

[Andy Brown]

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

 

[Da Moose]

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.

 

[David McRitchie]

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.

 

[Myrna Larson]

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.

 

[JMay]

Thanks for this "NEEDED" clarification.

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.

 

[Gren Goodwin]

Have you tried clearing the apparently blank cells?
Right click on 'dodgy' cell and use the 'clear contents '
option


Gren

 

[Paul Simon]

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

Thanks for this "NEEDED" clarification.