How do I generate a "blank" value?

[LawrenceHG]

I have a formula that I want to use to generate (depending on the situation)
a blank value. I thought a null string ("") was equivalent to a blank but
that doesn't seem to be the case.

Here's my example:

Contents of cell A2: =IF(A1=0,"",A1)

I want to be able to do a a Copy...Paste Special...Values with cell A2 and
have it copy a blank to the destination cell if cell A1 contains a zero.

In the above example, if I copy the value of cell A2 into cell A3, the
ISBLANK function tells me that cell A3 is not blank (i.e., it returns FALSE).
If I use the LEN string function, it returns a zero (i.e., a string of no
length).

Can someone explain to me the logic behind this? Does someone know what I
can enter into the IF function above so that it return a blank value (at
least it's blank when the value is copied to another cell)?

Thanks for any and all help.

[Harlan Grove]

"LawrenceHG" <[email protected]> wrote...
>I have a formula that I want to use to generate (depending on the
>situation)
>a blank value. I thought a null string ("") was equivalent to a blank but
>that doesn't seem to be the case.
....
>Can someone explain to me the logic behind this? Does someone know what I
>can enter into the IF function above so that it return a blank value (at
>least it's blank when the value is copied to another cell)?

This is one of those things Excel can't do. If a cell contains a formula,
ipso facto it can't be blank. There's no value that can be produced by a
formula that's equivalent to the value of blank cells (which do have values,
apparently the same value as VBA's Empty variant value).

Why do you need values equal to truly blank cells? Graphing? If so, #N/A
produces the same graphed results as blank cells and can be produced by
formulas.

[LawrenceHG]

Thanks for the response, Harlan.

The truth of the matter is that I can work around this problem with minimal
difficulty. I just found the problem itself to be curious (and inconvenient)
and I wanted to understand what was going on for future reference.

(For whatever it's worth, the actual application is as follows. I have a
matrix containing numbers greater than or equal to zero. I need another
matrix in which each element is "1" if the correspoinding entry in the
original matrix is positive. For those entries that are zero, I'd like to
have the first matrix have blanks, primarily for readability--the 1's are
what's really important. However, to use the matrix in calculations, I will
then need to convert the blanks to zeroes. I figured if I used blanks, it
would be easy to then use Select Special to highlight all the blanks so I
could change them to zero. Of course, I could use a space character instead
of a blank and then use the Excel Replace command to replace the space
character with a zero, but I preferred using blanks. Apparently no such blank
can be generated by a formula. Strange.)

"Harlan Grove" wrote:
>
> This is one of those things Excel can't do. If a cell contains a formula,
> ipso facto it can't be blank. There's no value that can be produced by a
> formula that's equivalent to the value of blank cells (which do have values,
> apparently the same value as VBA's Empty variant value).
>
> Why do you need values equal to truly blank cells? Graphing? If so, #N/A
> produces the same graphed results as blank cells and can be produced by
> formulas.
>
>
>

[Tushar Mehta]

If the primary interest is having a zero appear blank use either the format
General;General;;@ (select the cells of interest then Format | Cells... |
Number tab) or set the overall display to 'no zeros' (Tools | Options... |
View tab | Window options section | uncheck 'Zero values').
--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
> Thanks for the response, Harlan.
>
> The truth of the matter is that I can work around this problem with minimal
> difficulty. I just found the problem itself to be curious (and inconvenient)
> and I wanted to understand what was going on for future reference.
>
> (For whatever it's worth, the actual application is as follows. I have a
> matrix containing numbers greater than or equal to zero. I need another
> matrix in which each element is "1" if the correspoinding entry in the
> original matrix is positive. For those entries that are zero, I'd like to
> have the first matrix have blanks, primarily for readability--the 1's are
> what's really important. However, to use the matrix in calculations, I will
> then need to convert the blanks to zeroes. I figured if I used blanks, it
> would be easy to then use Select Special to highlight all the blanks so I
> could change them to zero. Of course, I could use a space character instead
> of a blank and then use the Excel Replace command to replace the space
> character with a zero, but I preferred using blanks. Apparently no such blank
> can be generated by a formula. Strange.)
>
> "Harlan Grove" wrote:
> >
> > This is one of those things Excel can't do. If a cell contains a formula,
> > ipso facto it can't be blank. There's no value that can be produced by a
> > formula that's equivalent to the value of blank cells (which do have values,
> > apparently the same value as VBA's Empty variant value).
> >
> > Why do you need values equal to truly blank cells? Graphing? If so, #N/A
> > produces the same graphed results as blank cells and can be produced by
> > formulas.
> >
> >
> >
>

[Harlan Grove]

Tushar Mehta wrote...
>If the primary interest is having a zero appear blank use either the format
>General;General;;@ (select the cells of interest then Format | Cells... |
>Number tab) or set the overall display to 'no zeros' (Tools | Options... |
>View tab | Window options section | uncheck 'Zero values').
....

FWIW, I've found it easier to determine intent for zero display
supression to use number formats that are more explicit, e.g.,
[=0]"";#,##0.00. The doubled double quotes after the [=0] condition are
necessary for conditional number formats; otherwise, missing formats
default to General. However, when negative numbers should display the
same as their absolute values except for including a leading hyphen,
using this kind of conditional number format means only needing to
specify the positive number format, so usually less work when
supressing zero display. Clarity, less typing. The only open issue
would be execution time differences.

[danswa]

I have a similar problem:

How do I set the text (value) of a cell to default ITSELF to a certain text when it is blank.

Something like =IF(ISBLANK(this),"empty cell",this)
Or a practical example:
Contents of cell A2: =IF(ISBLANK(A2),"empty cell",A2)

My current experiments return 0 as result

Help?

[George]

danswa wrote:
> I have a similar problem:
>
> How do I set the text (value) of a cell to default ITSELF to a certain
> text when it is blank.
>
> Something like =IF(ISBLANK(this),"empty cell",this)
> Or a practical example:
> Contents of cell A2: =IF(ISBLANK(A2),"empty cell",A2)
>
> My current experiments return 0 as result
>
> Help?
>
>
The reason you are getting zero is because you have created a circular
reference to yourself

Putting your statement in B2 works fine
B2: =IF(ISBLANK(A2),"empty cell",A2)

This method is used frequently in excel and you base the rest of your
calculations on column B not A

One common method I use for example is;
Column A - contains MANUAL DATA Entry values
Column B - contains CALCULATED formulas with possible default values
Column C - contains either manual entry or default calculated or blank
Then you use column C as you the basis for any further calculations

C1: = IF(ISBLANK(A1),IF(ISBLANK(B1),"BLANK",B1),A1)

Hope this helps
George

[Mike F.]

I have a major issue with this different interpretation of "null", relating
to the operation of Paste > Special > Values. I need a way to make all such
cells consistent for behavior of validating formulas and VBA code.

I do a lot of string manipulation functions to clean up data. In many cases
I am dealing with "empty" cells that are that way because they were unused,
and in other cases because a formula returned ="". If you do a copy of these
cells, and do a Paste > Special > Values I would expect the result to be
controllable, but it isn't. P>S>V puts a null string in the target cell if
the source was a formula that evaluated to ="", and it will put an "empty"
cell if the source was empty (cleared).

This causes three problems: 1) These cells do not test the same as a "real"
empty cell, 2) the "null string" cell does not allow the preceding cell text
to flow into the following cell, and 3) these two types of cells do not Sort
together nor do Filters and Pivot Tables treat them the same.

If you have cell A1 that you press Delete in, and cell A2 that you do a
Copy>Paste>Values of an empty string in (=""), visually they are identical on
the formula bar, but...

Type(A1) = 1 Type (A2) = 2
Isblank(A1) = True IsBlank(A2) = False
=A1="" = True =A2="" = True
Cells like A2 will sort before text and empty cells will sort after text.

The final slap in the face is that if you press F2 and then enter on the
"pasted null string" cell, it changes to an empty cell!

I can accept, begrudgingly, that this is "the way Excel works". But there
has to be some type of EQUALIZER -- either function or VBA, that will let me
go through 27,000 rows and 38 columns of data and make all the "visually
empty" cells act the same. If Paste Values doesn't do it, then the only
alternative is to press F2-Enter on every cell. :-O (or read the whole
thing in and write it back out to a different sheet with VBA that does the
correct checks on each cell.

Help!

- Mike

"Harlan Grove" wrote:

> "LawrenceHG" <[email protected]> wrote...
> >I have a formula that I want to use to generate (depending on the
> >situation)
> >a blank value. I thought a null string ("") was equivalent to a blank but
> >that doesn't seem to be the case.
> ....
> >Can someone explain to me the logic behind this? Does someone know what I
> >can enter into the IF function above so that it return a blank value (at
> >least it's blank when the value is copied to another cell)?
>
> This is one of those things Excel can't do. If a cell contains a formula,
> ipso facto it can't be blank. There's no value that can be produced by a
> formula that's equivalent to the value of blank cells (which do have values,
> apparently the same value as VBA's Empty variant value).
>
> Why do you need values equal to truly blank cells? Graphing? If so, #N/A
> produces the same graphed results as blank cells and can be produced by
> formulas.
>
>
>

[Harlan Grove]

Mike F. wrote...
....
>I am dealing with "empty" cells that are that way because they were unused,
>and in other cases because a formula returned ="". If you do a copy of these
>cells, and do a Paste > Special > Values I would expect the result to be
>controllable, but it isn't. P>S>V puts a null string in the target cell if
>the source was a formula that evaluated to ="", and it will put an "empty"
>cell if the source was empty (cleared).

Converting cells from formulas to values should leave other formulas
that refer to those cells unchanged. For example, if A1 contained
=2*ROW(), and A2 contained =A1+1, then A2 would evaluate to 3. If A1 is
converted to its formula's value, A2 should still evaluate to 3. By the
same toekn, if B1 contained =LEFT(A1,0), so evaluated to "", and B2
contained =AND(LEN(B1)=0,COUNTA(B1)=1), B2 would evaluate to TRUE. If
B1 were replaced with its formula's value, then B2 should still
evaluate to TRUE. However, that requires that B1 *not* be blank
('Empty' means something precise in VBA, but not in cell formulas; in
cell formulas, 'blank' means the state of cells that contain nothing),
and that requires replacing anything evaluating to "" with something
rather than nothing.

This is complicated by the fact that Excel converts blank cells to
numeric zeros, 0, in numeric contexts and to zero length strings, "",
in text contexts. Lotus 123 did much better by *always* treating them
as 0 and *never* as "". In programming languages, consistency is always
a good thing.

Back to pasting ="" and equivalent formulas as values. The result is
one of Excel's odd possible values, the zero length text constant.

>This causes three problems: 1) These cells do not test the same as a "real"
>empty cell, 2) the "null string" cell does not allow the preceding cell text
>to flow into the following cell, and 3) these two types of cells do not Sort
>together nor do Filters and Pivot Tables treat them the same.
....

Replace 'empty' with 'blank'. Empty is necessarily ambiguous. You may
know what you mean, but your usage isn't tied to Excel's documentation.

These 'problems' are all well-known. They're predictable functionality
that others may rely on to behave as they currently do. Your #2 is, in
fact, sometimes desirable.

>The final slap in the face is that if you press F2 and then enter on the
>"pasted null string" cell, it changes to an empty cell!

No different than entering the formula

="=1+2"

copying, pasting as value on top of itself, then pressing [F2],
[Enter]. Or for that matter, entering =RAND() in a cell originally with
number format General, then changing the cell's number format to Text
and pressing [F2], [Enter]. The point is that re-entering cells *can*
change their types and values even if no changes are made to the cell's
contents.

>I can accept, begrudgingly, that this is "the way Excel works". But there
>has to be some type of EQUALIZER -- either function or VBA, that will let me
>go through 27,000 rows and 38 columns of data and make all the "visually
>empty" cells act the same. If Paste Values doesn't do it, then the only
>alternative is to press F2-Enter on every cell. :-O (or read the whole
>thing in and write it back out to a different sheet with VBA that does the
>correct checks on each cell.

There's always macros.

Sub foo()
Dim r As Range
Application.Calculation = xlCalculationManual
For Each r In ActiveSheet.UsedRange
If r.Formula = "" And r.PrefixCharacter = "" _
And Not IsEmpty(r.Value) Then r.ClearContents
Next r
Application.Calculation = xlCalculationAutomatic
End Sub

[Dave Peterson]

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab
Toggle Transition Navigation keys on.

Then select on of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

If you need to do this lots, you can record a macro when you do it manually.



Mike F. wrote:
>
> I have a major issue with this different interpretation of "null", relating
> to the operation of Paste > Special > Values. I need a way to make all such
> cells consistent for behavior of validating formulas and VBA code.
>
> I do a lot of string manipulation functions to clean up data. In many cases
> I am dealing with "empty" cells that are that way because they were unused,
> and in other cases because a formula returned ="". If you do a copy of these
> cells, and do a Paste > Special > Values I would expect the result to be
> controllable, but it isn't. P>S>V puts a null string in the target cell if
> the source was a formula that evaluated to ="", and it will put an "empty"
> cell if the source was empty (cleared).
>
> This causes three problems: 1) These cells do not test the same as a "real"
> empty cell, 2) the "null string" cell does not allow the preceding cell text
> to flow into the following cell, and 3) these two types of cells do not Sort
> together nor do Filters and Pivot Tables treat them the same.
>
> If you have cell A1 that you press Delete in, and cell A2 that you do a
> Copy>Paste>Values of an empty string in (=""), visually they are identical on
> the formula bar, but...
>
> Type(A1) = 1 Type (A2) = 2
> Isblank(A1) = True IsBlank(A2) = False
> =A1="" = True =A2="" = True
> Cells like A2 will sort before text and empty cells will sort after text.
>
> The final slap in the face is that if you press F2 and then enter on the
> "pasted null string" cell, it changes to an empty cell!
>
> I can accept, begrudgingly, that this is "the way Excel works". But there
> has to be some type of EQUALIZER -- either function or VBA, that will let me
> go through 27,000 rows and 38 columns of data and make all the "visually
> empty" cells act the same. If Paste Values doesn't do it, then the only
> alternative is to press F2-Enter on every cell. :-O (or read the whole
> thing in and write it back out to a different sheet with VBA that does the
> correct checks on each cell.
>
> Help!
>
> - Mike
>
> "Harlan Grove" wrote:
>
> > "LawrenceHG" <[email protected]> wrote...
> > >I have a formula that I want to use to generate (depending on the
> > >situation)
> > >a blank value. I thought a null string ("") was equivalent to a blank but
> > >that doesn't seem to be the case.
> > ....
> > >Can someone explain to me the logic behind this? Does someone know what I
> > >can enter into the IF function above so that it return a blank value (at
> > >least it's blank when the value is copied to another cell)?
> >
> > This is one of those things Excel can't do. If a cell contains a formula,
> > ipso facto it can't be blank. There's no value that can be produced by a
> > formula that's equivalent to the value of blank cells (which do have values,
> > apparently the same value as VBA's Empty variant value).
> >
> > Why do you need values equal to truly blank cells? Graphing? If so, #N/A
> > produces the same graphed results as blank cells and can be produced by
> > formulas.
> >
> >
> >

--

Dave Peterson

[John James]

Dave,

Interesting that only the blank and zero length text constants in the used area of the worksheet are impacted by your method - even if a range exceeding the used range is selected (which comprises blanks).

Can only attribute this to efficient design in how the find/replace process works (hopefully)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

[Dave Peterson]

It works just like edit|find works--which is limited to the usedrange--but in
this case, why would you want to fix cells that have never been broken <bg>?

(So it sounds like that you're happy that MS does something the way you like
<vvbg>.)



John James wrote:
>
> Dave,
>
> Interesting that only the blank and zero length text constants in the
> used area of the worksheet are impacted by your method - even if the
> range exceeding the used range is selected (which comprises blanks).
>
> Can only attribute this to efficient design in how the find/replace
> process works (hopefully)
>
> Dave Peterson Wrote:
> >
> > When I want to clean up this detritus, I do this:
> >
> > Select the range (ctrl-a a few times to select all the cells)
> > Edit|Replace
> > what: (leave blank)
> > with: $$$$$
> > replace all
> >
> > Immediately followed by:
> > Edit|Replace
> > what: $$$$$
> > with: (leave blank)
> > replace all
> >
>
> Dave Peterson Wrote:
> >
> > When I want to clean up this detritus, I do this:
> >
> > Select the range (ctrl-a a few times to select all the cells)
> > Edit|Replace
> > what: (leave blank)
> > with: $$$$$
> > replace all
> >
> > Immediately followed by:
> > Edit|Replace
> > what: $$$$$
> > with: (leave blank)
> > replace all
> >
>
> --
> John James
> ------------------------------------------------------------------------
> John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
> View this thread: http://www.excelforum.com/showthread...hreadid=502218

--

Dave Peterson

[Mike F.]

Thanks! The Macro is a great idea.

- Mike