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?

Tools, options, transition tab, transition navigation keys is apparently
turned on. This is placing a Lotus alignment code in the cell.
' (apostrophe) Left align data in the cell
The alignment codes display only in the formula bar - not in the cell.

"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?

I have checked the transition tab and "Transition navigation keys" are
unchecked.
Under "Sheet options" Transition formula evaluation and "formula entry" are
also unchecked.

"BoniM" wrote:

Tools, options, transition tab, transition navigation keys is apparently
turned on. This is placing a Lotus alignment code in the cell.
' (apostrophe) Left align data in the cell
The alignment codes display only in the formula bar - not in the cell.

"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?

What type of Paste/Special did you perform?
--
Gary''s Student - gsnu200717


"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?

Paste Special " Values" and also tried "Values and number formats". Made no
difference.

"Gary''s Student" wrote:

What type of Paste/Special did you perform?
--
Gary''s Student - gsnu200717


"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?

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

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

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

Haven't read all of this but have you tried

range("a2").formula=range("a2").value

--
Don Guillett
SalesAid Software

"Dave Peterson" wrote in message
...
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

I think Dave has come up with the solution.
"I think it's that pesky ' that you use to make the formula text."
Avoiding it seems to be a good idea.
See his last posting.



"Don Guillett" wrote:


Haven't read all of this but have you tried

range("a2").formula=range("a2").value

--
Don Guillett
SalesAid Software

"Dave Peterson" wrote in message
...
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