Unless you have a very good reason to enable them, you should always
have the transition settings turned off. In Excel, the single
apostrophe (') is an indicator to Excel that the data that follows is
literal and should not be translated. The apostrophe does not show in
the cell. For example, if you enter 5/3/2008 in a cell, Excel
translates to a date value. If you enter '5/3/2008, Excel leaves it as
it is and does no translation. Since the apostrophe is not shown in
the cell, you must use two apostrophes to display one leading
apostrophe. For example, enter

'5/2/2008 displays as 5/2/2008
''5/2/2008 displays as '5/2/2008

Note that in the second example, the value leads with two single
apostrophes, not a single double quote character. In general, if the
first character entered in a cell is an apostrophe, that apostrophe
will not be displayed in the cell but everything else, including
subsequent apostrophes, will be displayed. In the formula bar, the
leading apostrophe is displayed so that you know it is there and so
that you can remove it if desired.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Wed, 05 Nov 2008 11:30:24 +1300, kirkm wrote:

Excel says "These characters (apostrophe) are formatting codes, which
may be displayed in documents created in Lotus 1-2-3 or if you are
working with keys that are used for navigation in Lotus 1-2-3."
And
"To hide the codes except label prefixes, click Options on the Tools
menu, click the Transition tab, and then clear the Transition
navigation keys check box"

Unchecking the Transition check box has no effect, although I don't
want to hide them, rather disable them. (No idea what a label prefix
is). The sheet was created in Excel, I don't have Lotus 1-2-3 or use
shortcut keys.

What's required is for the cell to display 'xyz', including
apostrophes. Entering this either in the formula bar, or the Cell, or
in VBA with 'Cell(x,y) =' results in the leading (prepended?)
apostrophe disappearing from the cell. It still shows in the formula
bar.

Entering two apostrophes goes some way to a fix, the first one
vanishes and the second one remains. But there's something intuitively
wrong when you have to enter a character twice, just to see it once.
Besides, there are some cells where a leading apostrophe DOES appear
in both the formula bar AND cell, but I can't see what's different
with them, or copy that formatting somewhere else. But it does prove
what I want, can be achieved - somehow.

Does anyone know what's going on, or alternately know somewhere else I
could ask?

Thanks - Kirk