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