The statement below was in the help menu for Excel. Try clearing your formatting in the cells or columns you are changing, then format as text.
Clear contents, formats, or comments from cells
1 Select the cells, rows, or columns you want to clear.
2 On the Edit menu, point to Clear, and then click All, Contents, Formats, or Comments.
· If you click a cell and then press DELETE or BACKSPACE, Microsoft Excel removes the cell contents but does not remove any comments or cell formats.
· If you clear a cell, Microsoft Excel removes the contents, formats, comments, or all three from a cell. The value of a cleared cell is 0 (zero), and a formula that refers to that cell will receive a value of 0.
Tip To remove all comments from a worksheet, click Go To on the Edit menu, click Special, and then click Comments. Then point to Clear on the Edit menu, and click Comments.
Yes, Yes, I do know about clearing the formats. The problem is that when I do the find and replace excel thinks the result (1/0) is a date and automatiacally formats the cells to date format even though I formatted them to TEXT previously. I'm not doing only one cell but thousands at the same time so the F2 / ENTER opioon is out of the question.
It's a real nasty one. My editor friend on the other side of the wall has no answer to the problem either.
On 2002-11-07 13:31, attc wrote:
(i) you must format as TEXT *before* removing the '-'
 sorry-- this doesn't work when you use edit->replace I just realised. SO...
(ii) replace the - with ' is the best solution.
AP - what did you mean? I get #DIV/0! when I enter -1/0 in a cell... as expected?!
I get the same as you (DIV/O). It automatically turns it into a formula, but when I manually remove the '=' then it leaves me with the result of '-1/0'. After doing some research, if you are planning on adjusting a large amount of cells then your best bet is to not use " - ", but " ' " as suggested.