Saving Some Numbers As CSV Changes To Dates

[Dazzawm]

I have some numbers in a normal excel sheet, but when I save as CSV it changes them to dates. How do I get round this please? Thanks.

For example

9-3 changes to 09-Mar
9-5 changes to 09-May

 

[Dazzawm]

So basically there's nothing really I can do about. As I am working in Excel its always going to change it to a date when saving as CSV. Thanks for your help.

 

[Andrew Poulsom]

I'll repeat for the last time. When you save a worksheet as csv what you see is what you get in the csv. Text like 9-3 won't be changed to a date in the csv. I don't know why you think that's not the case.

 

[Dazzawm]

I'll repeat for the last time. When I have the excel sheet it is 9-3 when I close and save as CSV and open the CSV file it is 09-Mar.

 

[Andrew Poulsom]

Excel converts it to a date when the csv is opened, but it's actually 9-3 in the csv. Open it in Notepad and you will see.

 

[joeu2004]

I'll repeat for the last time. When I have the excel sheet it is 9-3 when I close and save as CSV and open the CSV file it is 09-Mar.

Let's try to make this simple for you.

1. Always use the Excel file (".xls", ".xlsx" or ".xlsm") when using Excel.

2. When you want to create a file for another program, save as CSV if that is what the other program requires.

3. But do not open the CSV file when using Excel. Use the XLS/XLSX/XLSM file instead.

The CSV file will contain 9-3, if that is how it appears in the Excel cell. So the other program will see 9-3. (What it does with that, we don't know.)

The Excel (XLSX) file will always contain 9-3.

The only problem you see is when you open the CSV file directly in Excel, Excel converts 9-3 to a date.

Again, that happens at open time, not at save time.

Finally, to reiterate, saving as TXT was a misdirection. Forget about it!

Unless the other program requires tab-separated data (TXT) instead of comma-separated data (CSV).

Caveat: In the CSV file, the text will appear as 9-3, not "9-3". If the other program requires the latter quoted string, you must do that yourself either manually (edit with Notepad) or with a VBA macro in Excel.

 

[joeu2004]

Clariyfing....

Let's try to make this simple for you.

1. Always use the Excel file (".xls", ".xlsx" or ".xlsm") when using Excel.

2. When you want to create a file for another program, save as CSV if that is what the other program requires.

3. But do not open the CSV file when using Excel. Use the XLS/XLSX/XLSM file instead.

"Let's try to make this simple".... Well, as simple as Excel permits, which is not simple.

Step 2 should be (when you want to create a file for another program):

2a. Save the Excel file first (as XLS, XLSX or XLSM).
2b. Save as CSV.
2c. Close the CSV file. Click on "Don't Save"; nothing has changed!
2d. Re-open the Excel file (XLS, XLSX or XLSM) if you want to continue to use the file in Excel.

Step #2c is necessary (to keep things simple) because after Step #2b, Excel has change the file extension of the open file to ".csv".

The point again is:

* When you want to use Excel, open the XLS, XLSX or XLSM file.

* Save as CSV only to create a file to be used by a program other than Excel. Do not use the CSV file in Excel.

Again, this is just to keep things simple.

 

[Andrew Poulsom]

You could also copy the worksheet to a new workbook, save that as csv and close it. That way you wouldn't have to reopen the xls.