I enter '1/1/2007 in A1; the ' makes this text
In B1 I entered =--F1; the double negation coheres the text to number
I am working in dd/mm/yyyy format so the number I get is 39083
When I format this as date it show the correct date
Now I can select B1 (or a whole range( use Copy followed by Paste Special
Value. Please note: you do not need to do this in a new column, you can
paste over the top of the formulas

The same method worked when I preformatted a cell as text and entered a date
I could not get you " "&A1 method to work
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Cheri" wrote in message
...
Here's what I have: Spreadsheet with column of currency that was
formatted
as "text". I inserted a column to the right - put in formula =" "&A1 and
the
numbers then appears as numbers (without the little green tab in the
corner).
Then I wanted to delete the first column but can't because formula refers
to
it. I inserted a third column, copied 2nd column and paste special
(values)
but the text tab reappears. Is there some way to eliminate the original
text
column or copy the second column and keep it formatted as just number or
currency?