Unwanted single quote displayed in Formula bar

[Guest]

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?

 

[Guest]

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.

 

[Guest]

What type of Paste/Special did you perform?

 

[Guest]

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

 

[Guest]

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

 

[Dave Peterson]

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.

 

[Guest]

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]

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

 

[Guest]

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]

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

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.

 

[Guest]

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]

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

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.

 

[Guest]

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]

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.

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]

Just to keep kicking this dead horse...

Are you sure that those apostrophes were the results of a formula being
converted to values?

I ran this and found a difference between typing the apostrophe and converting
to values:

Option Explicit
Sub testme()
With Workbooks.Add(1).Worksheets(1).Range("a1")
.Clear
Debug.Print "after clear: *" _
& .PrefixCharacter & "*-- = " & Len(.PrefixCharacter)
.Value = "'"
Debug.Print "after apostrophe: *" _
& .PrefixCharacter & "*-- = " & Len(.PrefixCharacter)
.Clear
.Formula = "="""""
Debug.Print "with formula: *" _
& .PrefixCharacter & "*-- = " & Len(.PrefixCharacter)
.Value = .Value
Debug.Print "after value = value: *" _
& .PrefixCharacter & "*-- = " & Len(.PrefixCharacter)

Debug.Print "----Typing in apostrophe manually--------------"
.Clear
Debug.Print "after clear: *" _
& .PrefixCharacter & "*-- = " & Len(.PrefixCharacter)
.Value = "'"
.Replace what:="", replacement:="$$$$$"
Debug.Print "after first replace: *" _
& .PrefixCharacter & "*-- = " & Len(.PrefixCharacter)
.Replace what:="$$$$$", replacement:=""
Debug.Print "after second replace: *" _
& .PrefixCharacter & "*-- = " & Len(.PrefixCharacter)

Debug.Print "----using a formula--------------"
.Clear
Debug.Print "after clear: *" _
& .PrefixCharacter & "*-- = " & Len(.PrefixCharacter)
.Formula = "="""""
.Value = .Value
.Replace what:="", replacement:="$$$$$"
Debug.Print "after first replace: *" _
& .PrefixCharacter & "*-- = " & Len(.PrefixCharacter)
.Replace what:="$$$$$", replacement:=""
Debug.Print "after second replace: *" _
& .PrefixCharacter & "*-- = " & Len(.PrefixCharacter)

.Parent.Parent.Close savechanges:=False
End With
End Sub

And got these results:

after clear: **-- = 0
after apostrophe: *'*-- = 1
with formula: **-- = 0
after value = value: **-- = 0
----Typing in apostrophe manually--------------
after clear: **-- = 0
after first replace: *'*-- = 1
after second replace: *'*-- = 1
----using a formula--------------
after clear: **-- = 0
after first replace: **-- = 0
after second replace: **-- = 0

 

[Guest]

I think the "horse" is still alive and doing well.

To answer your question, yes those apostrophes were the results of a
formula being converted to values?
I have been doing some futher investigation.
It is easy for me to reproduce this problem.
I am using excel 2002 and start with a new blank Sheet.

With Tools/Options/Transition/Transition navigation keys unchecked

All cells are formatted under Number tab with General category.

I type into cell A1 the text "test"
into cell B1 the formula =IF(A1="x",A1,"")
into cell C1 =ISBLANK(B1)
and into D1 =CODE(B1)

Now after pressing the enter key in B1 the cell appears" blank" but contains
the result of the formula.

Next selecting B1 right click COPY and without moving to another cell
location
right click again and do a Paste/Special/Values.
With B1 still selected
I get a blank looking cell B1 but with ' in the formula bar.
Had I pasted/special/values in another location there would be no
problem:the formula bar and cell would both be blank.
Hope you can reproduce this.

Just to keep kicking this dead horse...

Are you sure that those apostrophes were the results of a formula being
converted to values?

I ran this and found a difference between typing the apostrophe and converting
to values:

Option Explicit
Sub testme()
With Workbooks.Add(1).Worksheets(1).Range("a1")
.Clear
Debug.Print "after clear: *" _
& .PrefixCharacter & "*-- = " & Len(.PrefixCharacter)
.Value = "'"
Debug.Print "after apostrophe: *" _
& .PrefixCharacter & "*-- = " & Len(.PrefixCharacter)
.Clear
.Formula = "="""""
Debug.Print "with formula: *" _
& .PrefixCharacter & "*-- = " & Len(.PrefixCharacter)
.Value = .Value
Debug.Print "after value = value: *" _
& .PrefixCharacter & "*-- = " & Len(.PrefixCharacter)

Debug.Print "----Typing in apostrophe manually--------------"
.Clear
Debug.Print "after clear: *" _
& .PrefixCharacter & "*-- = " & Len(.PrefixCharacter)
.Value = "'"
.Replace what:="", replacement:="$$$$$"
Debug.Print "after first replace: *" _
& .PrefixCharacter & "*-- = " & Len(.PrefixCharacter)
.Replace what:="$$$$$", replacement:=""
Debug.Print "after second replace: *" _
& .PrefixCharacter & "*-- = " & Len(.PrefixCharacter)

Debug.Print "----using a formula--------------"
.Clear
Debug.Print "after clear: *" _
& .PrefixCharacter & "*-- = " & Len(.PrefixCharacter)
.Formula = "="""""
.Value = .Value
.Replace what:="", replacement:="$$$$$"
Debug.Print "after first replace: *" _
& .PrefixCharacter & "*-- = " & Len(.PrefixCharacter)
.Replace what:="$$$$$", replacement:=""
Debug.Print "after second replace: *" _
& .PrefixCharacter & "*-- = " & Len(.PrefixCharacter)

.Parent.Parent.Close savechanges:=False
End With
End Sub

And got these results:

after clear: **-- = 0
after apostrophe: *'*-- = 1
with formula: **-- = 0
after value = value: **-- = 0
----Typing in apostrophe manually--------------
after clear: **-- = 0
after first replace: *'*-- = 1
after second replace: *'*-- = 1
----using a formula--------------
after clear: **-- = 0
after first replace: **-- = 0
after second replace: **-- = 0

 

[Dave Peterson]

I use xl2003, but it worked the same way in xl2002 for me.

I put the info in A1:d1.
(and all the options in tools|Options|transitions tab unchecked)

A1 showed: text
b1 looked empty
c1 showed: false
d1 showed: #value!

Is that what you got?

Then I converted B1 to values.
The formula bar looked empty with B1 selected
A1:B1 still displayed the same results.

Do you have any other options checked in tools|Options|transtions tab checked?

And you did this test on a brand new worksheet in a brand new workbook?

I think the "horse" is still alive and doing well.

To answer your question, yes those apostrophes were the results of a
formula being converted to values?
I have been doing some futher investigation.
It is easy for me to reproduce this problem.
I am using excel 2002 and start with a new blank Sheet.

With Tools/Options/Transition/Transition navigation keys unchecked

All cells are formatted under Number tab with General category.

I type into cell A1 the text "test"
into cell B1 the formula =IF(A1="x",A1,"")
into cell C1 =ISBLANK(B1)
and into D1 =CODE(B1)

Now after pressing the enter key in B1 the cell appears" blank" but contains
the result of the formula.

Next selecting B1 right click COPY and without moving to another cell
location
right click again and do a Paste/Special/Values.
With B1 still selected
I get a blank looking cell B1 but with ' in the formula bar.
Had I pasted/special/values in another location there would be no
problem:the formula bar and cell would both be blank.
Hope you can reproduce this.

 

[Guest]

To answer your questions.
"Is that what you got?" Yes.
"Do you have any other options checked in tools|Options|transtions tab
checked?"
No, except "Microsoft Excel menus radio button" is the only thing selected.
"And you did this test on a brand new worksheet in a brand new workbook?"
No. Only on the same sheet.

I next tried it on a new workbook, new sheet and behold, like your results,
there was no problem!

So I retraced my steps.
What I did on a new workbook/ sheet was to write the formula in another cell
say E1 for display purposes.
E1 cell had in it '=IF(A1="x",A1,"") as text.
I copied E1 and pasted it into B1 and then removed the '
It is then, if you go through the procedure (Copy/PasteSpecial/Values) that
the problem arises in the formula bar.
From then on, after deleting the contents of B1, and actually typing in the
same formula into B1
and following the same procedure produces the single quote probem on that
sheet.
It now becomes the offending sheet.
I hope you can now reproduce this problem.
If not, I can always send you the offending workbook.

 

[Dave Peterson]

It still worked fine for me.

If you want to send me a private email, just remove the xspam.

To answer your questions.
"Is that what you got?" Yes.
"Do you have any other options checked in tools|Options|transtions tab
checked?"
No, except "Microsoft Excel menus radio button" is the only thing selected.
"And you did this test on a brand new worksheet in a brand new workbook?"
No. Only on the same sheet.

I next tried it on a new workbook, new sheet and behold, like your results,
there was no problem!

So I retraced my steps.
What I did on a new workbook/ sheet was to write the formula in another cell
say E1 for display purposes.
E1 cell had in it '=IF(A1="x",A1,"") as text.
I copied E1 and pasted it into B1 and then removed the '
It is then, if you go through the procedure (Copy/PasteSpecial/Values) that
the problem arises in the formula bar.
From then on, after deleting the contents of B1, and actually typing in the
same formula into B1
and following the same procedure produces the single quote probem on that
sheet.
It now becomes the offending sheet.
I hope you can now reproduce this problem.
If not, I can always send you the offending workbook.

<<snipped>>

 

[Don Guillett]

Haven't read all of this but have you tried

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