Excel is interpreting my TEXT... I wish it would not!
"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
|