Real Meaning of Apostrophe?

[Josh Sale]

I've used a leading apostrophe (single quote) in my cells for years as a way
to keep Excel from converting a sequence of characters that look like a
number or a date to a number or a date. But I never really thought very
deeply about what was going on until today.

Here are my new test cases:

If I have a "General" cell with a value of 123, Copy it and then do a Paste
Special | Formats onto another General cell with a value of '456, after the
Paste Special, the target cell now has a value of 456 (i.e., the apostrophe
is gone). Why is this? Is the apostrophe in fact a format? If so, what
kind?

If I have a General cell with a value '123, Copy it and then do a Paste
Special | Formats onto another General cell with a value of '456, after the
Paste Special the target cell still has value of '456. Why does the
apostrophe remain in this case?

Finally, if I have a General cell with a value '123, Copy it and then do a
Paste Special | Formats onto another General cell with a value of 456, after
the Paste Special the target cell still has value of 456. Why doesn't the
apostrophe get copied in this case?

TIA,

josh

 

[Jim Thomlinson]

It is there for compatibility with Lotus 123. It is not truely a format. It
is an indicator that the value in the cell is not numeric and is not subject
to calculation. That is all it means. It does not change the format of the
cell. It does not change anything about the cell. Lotus does not require the
= sign to indicate a formula. So if you wanted a cell to show 2 + 3 then you
needed to type it in '2 + 3 otherwise it would display the 5...

 

[Josh Sale]

Thanks Jim.

But if its not a format, why does Edit | Paste Special | Format seem to do
different things depending on if the source and target cells have a leading
apostrophe?

josh

 

[Jim Thomlinson]

Couldn't tell ya.. I have not used apostrophes since I left Lotus many years
ago...
--
HTH...

Jim Thomlinson

Thanks Jim.

But if its not a format, why does Edit | Paste Special | Format seem to do
different things depending on if the source and target cells have a leading
apostrophe?

josh

 

[Tom Ogilvy]

from Excel help:

An apostrophe ('), a caret (^), or a quotation mark (") appears in the
formula bar but not in the cells.
These characters 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. In Lotus 1-2-3 formats, an apostrophe (')
indicates left-aligned text, a quotation mark (") indicates right-aligned
text, and a caret (^) indicates centered text.

To remove the codes, click Options on the Tools menu, click the Transition
tab, and then clear the Transition navigation keys check box.

----------------------------------------

From VBA help for the PREFIXCHARACTER property of a range object:



Returns the prefix character for the cell. Read-only Variant.

Remarks

If the TransitionNavigKeys property is False, this prefix character will be
' for a text label, or blank. If the TransitionNavigKeys property is True,
this character will be ' for a left-justified label, " for a right-justified
label, ^ for a centered label, \ for a repeated label, or blank.

--

Regards,

Tom Ogilvy

Thanks Jim.

But if its not a format, why does Edit | Paste Special | Format seem to do
different things depending on if the source and target cells have a
leading apostrophe?

josh

 

[trice-nae]

Interesting - and I actually have a further question on this topic.
Sometimes when I want to display (and print) the formulas in cells,
I've been known to to Find = and replace with '=. But after I've
printed and I want the formulas to be formulas again, doing FIND '=
REPLACE with = doesn't work. Why?

from Excel help:

An apostrophe ('), a caret (^), or a quotation mark (") appears in the
formula bar but not in the cells.
These characters 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. In Lotus 1-2-3 formats, an apostrophe (')
indicates left-aligned text, a quotation mark (") indicates right-aligned
text, and a caret (^) indicates centered text.

To remove the codes, click Options on the Tools menu, click the Transition
tab, and then clear the Transition navigation keys check box.

----------------------------------------

From VBA help for the PREFIXCHARACTER property of a range object:



Returns the prefix character for the cell. Read-only Variant.

Remarks

If the TransitionNavigKeys property is False, this prefix character will be
' for a text label, or blank. If the TransitionNavigKeys property is True,
this character will be ' for a left-justified label, " for a right-justified
label, ^ for a centered label, \ for a repeated label, or blank.

--

Regards,

Tom Ogilvy

 

[Tom Ogilvy]

Because it is being seen as a formatting character and not part of the value
of the cell.

 

[Jim Thomlinson]

To view formulas use Ctrl + ~

 

[Josh Sale]

Thanks Tom. I have a follow-up question:

If the apostrophe is seen as formatting, why doesn't it get copied when I do
a Copy from a cell that contains the apostrophe and an Edit | Paste Special
| Format to a cell that doesn't have one? If its truely a "format" it seems
like it should especially since the reverse is true (i.e., when I do a Copy
from a cell that doesn't contain an apostrophe and an Edit | Paste Special |
Format to a cell that does, the apostrophe is removed from the target cell).

Thanks again,

josh

 

[Tom Ogilvy]

It isn't formatting in the same sense as cell formatting; it is a prefix
character that is used to control the justification of a text string when
Lotus 1-2-3 compatability is in effect. In Excel itself it is interpreted
more as a signal to treat the entry as a string. You can use the format
command to right justify it and the apostrophe is still there. I can't say
about the paste special => format. I would say, what happens happens - not
sure there is a deeper meaning.

 

[Josh Sale]

Got it.

Thanks.

It isn't formatting in the same sense as cell formatting; it is a prefix
character that is used to control the justification of a text string when
Lotus 1-2-3 compatability is in effect. In Excel itself it is interpreted
more as a signal to treat the entry as a string. You can use the format
command to right justify it and the apostrophe is still there. I can't
say about the paste special => format. I would say, what happens
happens - not sure there is a deeper meaning.