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

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

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

I noticed that I can get rid of the single quote (in cell C5) in the formula
bar by
using the backspace key. So it seems that it really is present in the cell
although it does not show up when not selected and it is not a character code
39.

Next I tried your VBA macro and this one works and gets rid of the single
quote over selected areas.


However I have tried $$$$$ method) again and again. It still does not work
for me.

In the very first Edit/Replace where you use what: (leave blank)
means that C5 won't be selected and replaced with $$$$$.

Therefore it should not work at the next stage when all $$$$$ are selected
and then replaced by blanks.


"Dave Peterson" wrote:

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

I've never seen the pair of edit|Replaces fail. I don't have a guess why it
fails for you.



Joe wrote:

I noticed that I can get rid of the single quote (in cell C5) in the formula
bar by
using the backspace key. So it seems that it really is present in the cell
although it does not show up when not selected and it is not a character code
39.

Next I tried your VBA macro and this one works and gets rid of the single
quote over selected areas.

However I have tried $$$$$ method) again and again. It still does not work
for me.

In the very first Edit/Replace where you use what: (leave blank)
means that C5 won't be selected and replaced with $$$$$.

Therefore it should not work at the next stage when all $$$$$ are selected
and then replaced by blanks.

"Dave Peterson" wrote:

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


--

Dave Peterson

But I am not saying that it is failing.
It is just that the Edit/Replace
refuses to select the cell C5 because it is not being told to do so by the
first step.
It (C5) really is not a blank cell but contains a single quote as indicated
by the formula bar.

"Dave Peterson" wrote:

I've never seen the pair of edit|Replaces fail. I don't have a guess why it
fails for you.



Joe wrote:

I noticed that I can get rid of the single quote (in cell C5) in the formula
bar by
using the backspace key. So it seems that it really is present in the cell
although it does not show up when not selected and it is not a character code
39.

Next I tried your VBA macro and this one works and gets rid of the single
quote over selected areas.

However I have tried $$$$$ method) again and again. It still does not work
for me.

In the very first Edit/Replace where you use what: (leave blank)
means that C5 won't be selected and replaced with $$$$$.

Therefore it should not work at the next stage when all $$$$$ are selected
and then replaced by blanks.

"Dave Peterson" wrote:

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


--

Dave Peterson

If I type a single apstrophe in a bunch of cells and do the edit|Replace (leave
blank with $$$$$), those cells got changed.

But the second edit|replace didn't fix the problem--I still saw the apostrophe
in the cell.

But if those cells were the result of formulas that evaluated to "", then this
technique has never failed to clean those cells for me.

Joe wrote:

But I am not saying that it is failing.
It is just that the Edit/Replace
refuses to select the cell C5 because it is not being told to do so by the
first step.
It (C5) really is not a blank cell but contains a single quote as indicated
by the formula bar.

"Dave Peterson" wrote:

I've never seen the pair of edit|Replaces fail. I don't have a guess why it
fails for you.



Joe wrote:

I noticed that I can get rid of the single quote (in cell C5) in the formula
bar by
using the backspace key. So it seems that it really is present in the cell
although it does not show up when not selected and it is not a character code
39.

Next I tried your VBA macro and this one works and gets rid of the single
quote over selected areas.

However I have tried $$$$$ method) again and again. It still does not work
for me.

In the very first Edit/Replace where you use what: (leave blank)
means that C5 won't be selected and replaced with $$$$$.

Therefore it should not work at the next stage when all $$$$$ are selected
and then replaced by blanks.

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson