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. PSV 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
CopyPasteValues 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" 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