I'd try it again. This has always worked for me.

Maybe you could test it in a test worksheet.

You could also use a macro that looks at each cell in the selection:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No constants in selection!"
Exit Sub
End If

For Each myCell In myRng.Cells
If len(mycell.value) = 0 then
mycell.value = ""
end if
Next myCell

End Sub

Select a range and try it out.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Joe wrote:

OK. When you do the last EDIT/Replace it does not change cell C5 since it
still contains '$$$$$ The apostrophe seems to be a real character here not
just a ghost on the toolbar. So it does not replace it.

"Dave Peterson" wrote:

That toggle allows you to see the apostrophe (or hide it). It doesn't remove it
at all.

And you didn't read the next portion:

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



Joe wrote:

Toggling the transition navigation key many times made no difference at all.

Using Edit/Replace with $$$$$ yielded in cell C5 '$$$$$

I suppose using NA() instead of "" circumvents the problem and is a
solution but is not an explanation of what else could be happening.

"Dave Peterson" wrote:

Saved from a previous post:

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

=================
If you know that you're going to convert to values when you're done, you could
modify your formulas to make it slightly easier:

=IF(MID(A5,LEN(A5),1)="q",A5&"ue",na())
or even
=IF(right(A5,1)="q",A5&"ue",na())

Then just change #n/a to nothing after you convert to values.


Joe wrote:

Cell A5 contains the text word TEST

Cell C5 contains the formula =IF(MID(A5,LEN(A5),1)="q",A5&"ue","")

The result is a "blank" or a "" type of cell content since the result is
FALSE.

Now if I select this cell and copy it and then PASTE/Special into the same
C5 position I get a single quote ' to appear on the formula bar but not in
the cell when C5 is selected.
Placing the formula = code(C5) into cell D5 gives #VALUE!
Does anyone have an explanation as to what might be going on here?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson