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

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...
--
HTH...

Jim Thomlinson


"Josh Sale" wrote:

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

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" wrote in message
...
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...
--
HTH...

Jim Thomlinson

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

Jim Thomlinson


"Josh Sale" wrote:

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" wrote in message
...
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...
--
HTH...

Jim Thomlinson

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



"Josh Sale" <jsale@tril dot cod wrote in message
...
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" wrote in
message ...
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...
--
HTH...

Jim Thomlinson

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?



Tom Ogilvy wrote:
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



"Josh Sale" <jsale@tril dot cod wrote in message
...
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" wrote in
message ...
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...
--
HTH...

Jim Thomlinson

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

--
Regards,
Tom Ogilvy


"trice-nae" wrote in message
ps.com...
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?



Tom Ogilvy wrote:
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



"Josh Sale" <jsale@tril dot cod wrote in message
...
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" wrote in
message ...
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...
--
HTH...

Jim Thomlinson

To view formulas use Ctrl + ~
--
HTH...

Jim Thomlinson


"trice-nae" wrote:

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?



Tom Ogilvy wrote:
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



"Josh Sale" <jsale@tril dot cod wrote in message
...
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" wrote in
message ...
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...
--
HTH...

Jim Thomlinson

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" wrote in message
...


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

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.

--
Regards,
Tom Ogilvy




"Josh Sale" <jsale@tril dot cod wrote in message
...
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" wrote in message
...


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