Resetting cell format from TEXT to NUMERIC and DATE
 

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 (FormatCellsselect
ANYTHING!!).
TIA,

Resetting cell format from TEXT to NUMERIC and DATE
 

Format all to General.

Copy an empty cell.

Select the range to change and EditPaste SpecialAddOKEsc.

Re-format date cells to date format you wish.


Gord Dibben MS Excel MVP

On Sun, 4 May 2008 19:34:00 -0700, Tom wrote:

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 (FormatCellsselect
ANYTHING!!).
TIA,

Resetting cell format from TEXT to NUMERIC and DATE
 

Try these...

For the dates...

Select the cells in question
Goto DataText to Columns
NextNext
Column data formatDateDMY
Finish

Format in the DATE style of your choice.

For the TEXT numbers...

Select the cells in question
Goto DataText 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

--
Biff
Microsoft Excel MVP


"Tom" wrote in message
...
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
(FormatCellsselect
ANYTHING!!).
TIA,