Excel changes imported string to date, even if it's not a date
Posted by Bruce Bottomley on January 30, 2001 7:27 PM
If Excel sees a field in an imported .csv file that LOOKS like a date, it turns it into a date.
Example: .csv field is 01/01 or "01/01". It's not a date--just some numbers separated by a slash.
Excel reads it as 1/1/2001
Can Excel be set to simply read this as a string of characters and not assume it's a date?
I can get around this by inserting a space before the first digit in the quoted version above, but then I have to further process it in Excel after importing the file. I'd like to just import the file correctly.
Posted by Dave Hawley on January 30, 2001 10:06 PM
Hi Bruce
Try formatting all cells as Text before importing.
Dave
OzGrid Business Applications
Posted by Mark W. on January 31, 2001 7:58 AM
Bruce, it's not clear to me how your creating these
values. Intead of saving "01/01" when you create
the .csv document, can you save ="01/01"?
Posted by Bruce Bottomley on February 01, 2001 2:13 PM
The .csv files come from other systems, unrelated to Excel. In one example, they are manufacturers' stock numbers, which have the unfortunate characteristic of sort of looking like dates.
We know that there are some tricks we can do if we preprocess these files, but the goal is to be able to import them directly.
Posted by Mark W. on February 01, 2001 2:29 PM
Bruce, there is one more approach that you ought to
consider. You could change the file extension to .txt,
open the file from within Excel, and use the Text to
Column wizard to apply the proper data type. In fact,
this activity could be recorded as a macro, and then
tweaked using the VBA editor.
Posted by Bruce Bottomley on February 01, 2001 2:30 PM
That works great for entering data into the spreadsheet, but importing a .csv entails opening a new sheet. Wouldn't that new sheet revert to the default format?
Have I been misusing the word "import"? I meant it to mean opening a spreadsheet from a .csv file using the file/open command. Is there a way to bring a .csv file into an already established spreadsheet, as one can do in word processors?
