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