Thread
:
Unwanted single quote displayed in Formula bar
View Single Post
#
22
Posted to microsoft.public.excel.misc
Joe
external usenet poster
Posts: 476
Unwanted single quote displayed in Formula bar
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
Reply With Quote
Joe
View Public Profile
Find all posts by Joe