reformatting "text" format for dates and numeric data

[Zz Yzx]

Hi all,

I need to manipulate a LOT of historic excel files that had a LOT of
different people that did the original data entry. The data are in
columns, and consist of dates (Column B) and numeric values (Columns
C- NN). But some of the date and numeric values are stored as text
(as indicated by the little green triangles in the upper left corner
of the cell), depending on who did the original data entry. When I
try to re-format the date or numeric data by selecting the column and
then format and then date or number) either manually or in a macro,
the format command does not reformat the cells that were originally
formatted as "text".

What am I missing? Any help appreciated,

Thanks a heap,
-Zx

 

[Jon Peltier]

Although Excel may change the format of the cell, it may still consider its
contents to be text. You can often coerce a conversion using text to
columns, just converting the column in place, or by adding zero to a text
cell, by copying a blank cell, then selecting the range of text, and using
paste special - operation - multiply.

- Jon

 

[Zz Yzx]

Although Excel may change the format of the cell, it may still consider its
contents to be text. You can often coerce a conversion using text to
columns, just converting the column in place, or by adding zero to a text
cell, by copying a blank cell, then selecting the range of text, and using
paste special - operation - multiply.

- Jon

Rodger that, hook. line, and sinker.

Thanks for the effort.

-Zz

 

[ward376]

For the numeric entries you can do something like:

With yourrange
.NumberFormat = "General"
.Value = .Value
End With

this will convert the dates, but depending on your regional settings
and what was entered into the cell, they may not be accurate. It will
convert anything excel recognizes as a date to the number that
represents the date that excel thinks it is.

Cliff Edwards