Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell formatting ends up scientific?
I have a question,
Sometimes when we import data from a comma separated file it will take a date field, say 8/24/06 and display it in a scientific format - like 3.90E+04. Why does it do this? Is there a way to add a spreadsheet function to reformat a column after the import takes place to get it back to a date format? Or I think it's done it when the field is 082406 or 08-24-06. Or it could be 2007-01-09 or 2007/01/09, etc. I think it does this even if you have predefined the column as "text" or "general" or even "date". I guess I have a hard time understanding why excel tries to outsmart me and how I can outsmart it instead. Thanks for any suggestions. ga George Applegate |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell formatting ends up scientific?
If you change the file extension to *.txt the text import wizard will start
and if you click next twice and select Date under column date format and the format of the imported date (not your default date format) so if the date is 082406 select MDY, if it is 2007-01-09 or 20070109 select YMD then it should work fine. Note that you don't have to rename the cell if you do a bit of a detour, assume you have saved the file on your hd and it is called *.CSV do dataimport external dataimport date and type *.* in the filename box, that will show all files, now find your *.CSV file and open it and the text import wizard will open -- Regards, Peo Sjoblom "George Applegate" wrote in message ... I have a question, Sometimes when we import data from a comma separated file it will take a date field, say 8/24/06 and display it in a scientific format - like 3.90E+04. Why does it do this? Is there a way to add a spreadsheet function to reformat a column after the import takes place to get it back to a date format? Or I think it's done it when the field is 082406 or 08-24-06. Or it could be 2007-01-09 or 2007/01/09, etc. I think it does this even if you have predefined the column as "text" or "general" or even "date". I guess I have a hard time understanding why excel tries to outsmart me and how I can outsmart it instead. Thanks for any suggestions. ga George Applegate |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting as TEXT as opposed to scientific notation | Excel Discussion (Misc queries) | |||
filter using "ends with" and 3 choices? Ex: ends with 1,2 or3 | Excel Worksheet Functions | |||
Number Formatting/Scientific notation | Excel Discussion (Misc queries) | |||
Cell changes from General format to Scientific | Excel Worksheet Functions | |||
Scientific Formatting takes over - how to stop... | Excel Discussion (Misc queries) |