How to convince Excel a cell is "blank"

DaMoose

unread,

Aug 3, 2003, 4:15:12 PM8/3/03

to

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

Andy Brown

unread,

Aug 3, 2003, 4:34:45 PM8/3/03

to

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

David McRitchie

unread,

Aug 3, 2003, 4:33:18 PM8/3/03

to

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"DaMoose" <shaz...@hotmail.com> wrote in message news:00c401c359fb$f1a82da0$a601...@phx.gbl...

Da Moose

unread,

Aug 3, 2003, 4:57:49 PM8/3/03

to

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

unread,

Aug 3, 2003, 5:13:49 PM8/3/03

to

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.
--

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Da Moose" <shaz...@hotmail.com> wrote in message news:01d701c35a01$e5f5e370$a101...@phx.gbl...

Myrna Larson

unread,

Aug 3, 2003, 6:14:43 PM8/3/03

to

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

unread,

Aug 3, 2003, 7:51:50 PM8/3/03

to

Thanks for this "NEEDED" clarification.

"Myrna Larson" <myrna...@charter.net> wrote in message
news:h92rivc2onutmepsm...@4ax.com...

Gren Goodwin

unread,

Aug 4, 2003, 6:59:59 AM8/4/03

to

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

Gren

>.
>

Paul Simon

unread,

Aug 4, 2003, 7:34:06 AM8/4/03

to

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" <jm...@cox.net> wrote in message news:<PfhXa.5476$qf.3701@lakeread06>...

Ron Bernstein

unread,

Aug 5, 2003, 12:28:33 PM8/5/03

to

Look at the information related to #N/A. I just used this
today, and EXCEL looks at that as no data in the cell. I
wrote #N/A into the cell if it was supposed to be blank.

>.
>