Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
reformatting "text" format for dates and numeric data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
reformatting "text" format for dates and numeric data
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Zz Yzx" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
reformatting "text" format for dates and numeric data
On Wed, 16 Apr 2008 21:03:47 -0400, "Jon Peltier"
wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
reformatting "text" format for dates and numeric data
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
retain custom date format "text" when pasting or reformatting data | Excel Discussion (Misc queries) | |||
Excel: Changing "numeric $" to "text $" in a different cell. | Excel Worksheet Functions | |||
If A3=alpha numeric,"X", if A3=text,"Y", Blank | Excel Worksheet Functions | |||
If A3=alpha numeric,"X", if A3=text,"Y", Blank | Excel Programming | |||
Stop autoformatted dates without using "format TEXT" or ' | Excel Discussion (Misc queries) |