Resetting cell format from TEXT to NUMERIC and DATE

[Tom]

Excel 2003 SP3
I downloaded some Yahoo data that is coming across as text. My spreadsheet
columns need to change the format to numeric or date depending on the column.
Here's the situation: there's a small green upward-left pointing arrow
signifying that the cell is text. The group of cells were copied/pasted from
Yahoo. (maybe next time I should paste-special??) I've attempting to
highlight all the cells and change them from text to numeric or text to date
but I'm unsuccessful. Dates are 1-May-08 instead of the 5/1/2008 format that
I desire. Numeric text is showing as 6.21 (text) vs the 6.21 that I desire
in a numeric format.

So, how can I remove the text (green arrow) format and replace it with the
proper numeric format. All things I've tried don't work (Format>Cells>select
ANYTHING!!).
TIA,

 

[Gord Dibben]

Format all to General.

Copy an empty cell.

Select the range to change and Edit>Paste Special>Add>OK>Esc.

Re-format date cells to date format you wish.


Gord Dibben MS Excel MVP

 

[T. Valko]

Try these...

For the dates...

Select the cells in question
Goto Data>Text to Columns
Next>Next
Column data format>Date>DMY
Finish

Format in the DATE style of your choice.

For the TEXT numbers...

Select the cells in question
Goto Data>Text to Columns
Click Finish

However, if you might have unseen "junk" like char 160 spaces that were
downloaded the above might not work. Whenever I download from websites I run
this macro to "clean" the data:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall