Leading Apostrophe appears in the formula bar but not in the cells

[kirkm]

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

 

[Chip Pearson]

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)

 

[Jon Peltier]

You could also use this:

="'xyz'"

which replaces the extra single quote with two double quotes and an equals
sign.

- Jon

 

[kirkm]

Chip, thanks for the run down which makes
sense and it what (mostly) happens.

But I *DO* have some instances when one leading apostrophe
exists and displays as such in both the the Cell and formular bar.

I'm hoping to find how this happened and how can I do it again. It's
very irritating because it's exactly what I want. The sheet's use is
for a static text display with no formulas, calculations or fancy
Excel things. I use the formual bar for editing and data entry (mainly
to avoid that icon thing that otherwise shows up) and it is just plain
wrong for it to be different to the cell.

I fel I'm in a losing battle though... !

Thanks - Kirk

 

[kirkm]

You could also use this:

="'xyz'"

I tried that, and found the quotes and equal chars
displayed in the Cell as well. Unless I did something wrong?

Cheers - Kirk

 

[SammyR]

I appreciate that this one’s a little old, but I couldn’t find a solution to this elsewhere. I also want to see what is in the formula bar appear in the cell, including the leading apostrophe. I have a sheet where some cells show it and others don’t.

The way around it is to create a cell in which the desired format is displayed in the cell (e.g. use the double apostrophe to show a single apostrophe). Then copy and paste values to the desired location.

Any editing of the cell with the cursor or hitting return within the cell causes the apostrophe to disappear from view.