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.

-----Original Message-----
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

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]

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

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.

-----Original Message-----
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

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

.

 

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

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.

-----Original Message-----
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

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

.

 

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

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.

-----Original Message-----
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

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


.

 

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

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.

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.


-----Original Message-----
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

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


.