software

I have a formula that I want to use to generate (depending on the situation)
a blank value. I thought a null string (quot;quot;) 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,quot;quot;,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.

quot;LawrenceHGquot; gt; wrote...
gt;I have a formula that I want to use to generate (depending on the
gt;situation)
gt;a blank value. I thought a null string (quot;quot;) was equivalent to a blank but
gt;that doesn't seem to be the case.
....
gt;Can someone explain to me the logic behind this? Does someone know what I
gt;can enter into the IF function above so that it return a blank value (at
gt;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.
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 quot;1quot; 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.)

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

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

Tushar Mehta wrote...
gt;If the primary interest is having a zero appear blank use either the format
gt;General;General;;@ (select the cells of interest then Format | Cells... |
gt;Number tab) or set the overall display to 'no zeros' (Tools | Options... |
gt;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]quot;quot;;#,##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.
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),quot;empty cellquot;,this)
Or a practical example:
Contents of cell A2: =IF(ISBLANK(A2),quot;empty cellquot;,A2)

My current experiments return 0 as result

Help?--
danswa
------------------------------------------------------------------------
danswa's Profile: www.excelforum.com/member.php...oamp;userid=31267
View this thread: www.excelforum.com/showthread...hreadid=502218danswa wrote:
gt; I have a similar problem:
gt;
gt; How do I set the text (value) of a cell to default ITSELF to a certain
gt; text when it is blank.
gt;
gt; Something like =IF(ISBLANK(this),quot;empty cellquot;,this)
gt; Or a practical example:
gt; Contents of cell A2: =IF(ISBLANK(A2),quot;empty cellquot;,A2)
gt;
gt; My current experiments return 0 as result
gt;
gt; Help?
gt;
gt;
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),quot;empty cellquot;,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),quot;BLANKquot;,B1),A1)

Hope this helps
George

I have a major issue with this different interpretation of quot;nullquot;, relating
to the operation of Paste gt; Special gt; 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 quot;emptyquot; cells that are that way because they were unused,
and in other cases because a formula returned =quot;quot;. If you do a copy of these
cells, and do a Paste gt; Special gt; Values I would expect the result to be
controllable, but it isn't. Pgt;Sgt;V puts a null string in the target cell if
the source was a formula that evaluated to =quot;quot;, and it will put an quot;emptyquot;
cell if the source was empty (cleared).

This causes three problems: 1) These cells do not test the same as a quot;realquot;
empty cell, 2) the quot;null stringquot; 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
Copygt;Pastegt;Values of an empty string in (=quot;quot;), visually they are identical on
the formula bar, but...

Type(A1) = 1 Type (A2) = 2
Isblank(A1) = True IsBlank(A2) = False
=A1=quot;quot; = True =A2=quot;quot; = 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
quot;pasted null stringquot; cell, it changes to an empty cell!

I can accept, begrudgingly, that this is quot;the way Excel worksquot;. 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 quot;visually
emptyquot; 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

quot;Harlan Grovequot; wrote:

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

Mike F. wrote...
....
gt;I am dealing with quot;emptyquot; cells that are that way because they were unused,
gt;and in other cases because a formula returned =quot;quot;. If you do a copy of these
gt;cells, and do a Paste gt; Special gt; Values I would expect the result to be
gt;controllable, but it isn't. Pgt;Sgt;V puts a null string in the target cell if
gt;the source was a formula that evaluated to =quot;quot;, and it will put an quot;emptyquot;
gt;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 quot;quot;, 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 quot;quot; 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, quot;quot;,
in text contexts. Lotus 123 did much better by *always* treating them
as 0 and *never* as quot;quot;. In programming languages, consistency is always
a good thing.

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

gt;This causes three problems: 1) These cells do not test the same as a quot;realquot;
gt;empty cell, 2) the quot;null stringquot; cell does not allow the preceding cell text
gt;to flow into the following cell, and 3) these two types of cells do not Sort
gt;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.

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

No different than entering the formula

=quot;=1 2quot;

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.

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

--

Dave Peterson