Try this - it look a lot like a bug to me:
set a cell to the format TEXT and fill it with '1-2' (in words, one minus
two).
If this had been a STANDARD cell format, Excel would interpret this value as
a date and modify it accordingly when you leave the cell, but quite corectly,
not if you specify the cell to be a TEXT cell... so far so good.
Now use the search and replace tool to surround the minus with spaces. We
would expect nothing more than the value '1 - 2'... unfortunatly what we get
is a date! Somehow Excel has ignored or overwritten the cell format.
I think this is wrong: a cell defined as TEXT should stay TEXT unless I
choose it to be otherwise. Yes, I could add a leading apostrophy, but the
search and replace tool is too weak to prepend all my texts for me!

On Tue, 6 Nov 2007 03:28:01 -0800, Puddles
wrote:

Try this - it look a lot like a bug to me:
set a cell to the format TEXT and fill it with '1-2' (in words, one minus
two).
If this had been a STANDARD cell format, Excel would interpret this value as
a date and modify it accordingly when you leave the cell, but quite corectly,
not if you specify the cell to be a TEXT cell... so far so good.
Now use the search and replace tool to surround the minus with spaces. We
would expect nothing more than the value '1 - 2'... unfortunatly what we get
is a date! Somehow Excel has ignored or overwritten the cell format.
I think this is wrong: a cell defined as TEXT should stay TEXT unless I
choose it to be otherwise. Yes, I could add a leading apostrophy, but the
search and replace tool is too weak to prepend all my texts for me!


I can confirm this happens in Excel 2002.
--ron

"Ron Rosenfeld" wrote in message
...
On Tue, 6 Nov 2007 03:28:01 -0800, Puddles

wrote:

Try this - it look a lot like a bug to me:
set a cell to the format TEXT and fill it with '1-2' (in words, one minus
two).
If this had been a STANDARD cell format, Excel would interpret this value
as
a date and modify it accordingly when you leave the cell, but quite
corectly,
not if you specify the cell to be a TEXT cell... so far so good.
Now use the search and replace tool to surround the minus with spaces. We
would expect nothing more than the value '1 - 2'... unfortunatly what we
get
is a date! Somehow Excel has ignored or overwritten the cell format.
I think this is wrong: a cell defined as TEXT should stay TEXT unless I
choose it to be otherwise. Yes, I could add a leading apostrophy, but the
search and replace tool is too weak to prepend all my texts for me!


I can confirm this happens in Excel 2002.
--ron

Same behavior in Excel 2003. I never noticed this before, and I can't
figure out a workaround!
James

"Puddles" wrote in message
...
Try this - it look a lot like a bug to me:
set a cell to the format TEXT and fill it with '1-2' (in words, one minus
two).
If this had been a STANDARD cell format, Excel would interpret this value
as
a date and modify it accordingly when you leave the cell, but quite
corectly,
not if you specify the cell to be a TEXT cell... so far so good.
Now use the search and replace tool to surround the minus with spaces. We
would expect nothing more than the value '1 - 2'... unfortunatly what we
get
is a date! Somehow Excel has ignored or overwritten the cell format.
I think this is wrong: a cell defined as TEXT should stay TEXT unless I
choose it to be otherwise. Yes, I could add a leading apostrophy, but the
search and replace tool is too weak to prepend all my texts for me!

The best I can suggest is to add leading apostrophes with a formula. For
text in column A, the formula (for, say, B1) would be
="'"&A1
(that is, an apostrophe between two double quotes followed by &A1).
This can then be copied down the rest of column B.
Then select column B, copy it and use
Edit Paste Special Values
This replaces the formulas with the resulting values. You can then delete
column A and use instead these resulting values.
Now, relacing "-" with " - " will change "1-2" to "1 - 2" as you require,
without converting it to a date.

In Excel 2007, the same.


On Nov 6, 8:54 am, "Zone" wrote:
"Ron Rosenfeld" wrote in message

...





On Tue, 6 Nov 2007 03:28:01 -0800, Puddles

wrote:

Try this - it look a lot like a bug to me:
set a cell to the format TEXT and fill it with '1-2' (in words, one minus
two).
If this had been a STANDARD cell format, Excel would interpret this value
as
a date and modify it accordingly when you leave the cell, but quite
corectly,
not if you specify the cell to be a TEXT cell... so far so good.
Now use the search and replace tool to surround the minus with spaces. We
would expect nothing more than the value '1 - 2'... unfortunatly what we
get
is a date! Somehow Excel has ignored or overwritten the cell format.
I think this is wrong: a cell defined as TEXT should stay TEXT unless I
choose it to be otherwise. Yes, I could add a leading apostrophy, but the
search and replace tool is too weak to prepend all my texts for me!

I can confirm this happens in Excel 2002.
--ron

Same behavior in Excel 2003. I never noticed this before, and I can't
figure out a workaround!
James- Hide quoted text -

- Show quoted text -

Judging by the replies to this post, we do indeed have a bug here! How does
one escalate this to the developers at Microsoft?

"Puddles" wrote:

Try this - it look a lot like a bug to me:
set a cell to the format TEXT and fill it with '1-2' (in words, one minus
two).
If this had been a STANDARD cell format, Excel would interpret this value as
a date and modify it accordingly when you leave the cell, but quite corectly,
not if you specify the cell to be a TEXT cell... so far so good.
Now use the search and replace tool to surround the minus with spaces. We
would expect nothing more than the value '1 - 2'... unfortunatly what we get
is a date! Somehow Excel has ignored or overwritten the cell format.
I think this is wrong: a cell defined as TEXT should stay TEXT unless I
choose it to be otherwise. Yes, I could add a leading apostrophy, but the
search and replace tool is too weak to prepend all my texts for me!

It appears to change the cell format to Custom, (d-mmm) and the value
becomes 1/2/2007 and if you then format back to text you get 39084!

This is in Excel 2003, but I suppose all are alike.

Puddles wrote:

Try this - it look a lot like a bug to me:
set a cell to the format TEXT and fill it with '1-2' (in words, one minus
two).
If this had been a STANDARD cell format, Excel would interpret this value as
a date and modify it accordingly when you leave the cell, but quite corectly,
not if you specify the cell to be a TEXT cell... so far so good.
Now use the search and replace tool to surround the minus with spaces. We
would expect nothing more than the value '1 - 2'... unfortunatly what we get
is a date! Somehow Excel has ignored or overwritten the cell format.
I think this is wrong: a cell defined as TEXT should stay TEXT unless I
choose it to be otherwise. Yes, I could add a leading apostrophy, but the
search and replace tool is too weak to prepend all my texts for me!