Excel CSV - Number cell format

I produce a report as an CSV file. When I try to open the file in Excel, it makes an assumption about the data type based on the contents of the cell, and reformats it accordingly.

For example, if the CSV file contains

...,005,...

Then Excel shows it as 5. Is there a way to override this and display 005?

I would prefer to do something to the file itself, so that the user could just double-click on the CSV file to open it.

I use Excel 2003.

Solution 1:

There isn’t an easy way to control the formatting Excel applies when opening a .csv file. However listed below are three approaches that might help.

My preference is the first option.

Option 1 – Change the data in the file

You could change the data in the .csv file as follows ...,=”005”,... This will be displayed in Excel as ...,005,...

Excel will have kept the data as a formula, but copying the column and using paste special values will get rid of the formula but retain the formatting

Option 2 – Format the data

If it is simply a format issue and all your data in that column has a three digits length. Then open the data in Excel and then format the column containing the data with this custom format 000

Option 3 – Change the file extension to .dif (Data interchange format)

Change the file extension and use the file import wizard to control the formats. Files with a .dif extension are automatically opened by Excel when double clicked on.

Step by step:

• Change the file extension from .csv to .dif
• Double click on the file to open it in Excel.
• The 'File Import Wizard' will be launched.
• Set the 'File type' to 'Delimited' and click on the 'Next' button.
• Under Delimiters, tick 'Comma' and click on the 'Next' button.
• Click on each column of your data that is displayed and select a 'Column data format'. The column with the value '005' should be formatted as 'Text'.
• Click on the finish button, the file will be opened by Excel with the formats that you have specified.

Solution 2:

Don't use CSV, use SYLK.
http://en.wikipedia.org/wiki/SYmbolic_LinK_(SYLK)

It gives much more control over formatting, and Excel won't try to guess the type of a field by examining the contents. It looks a bit complicated, but you can get away with using a very small subset.

Solution 3:

This works for Microsoft Office 2010, Excel Version 14

I misread the OP's preference "to do something to the file itself." I'm still keeping this for those who want a solution to format the import directly

1. Open a blank (new) file (File -> New from workbook)
   
2. Open the Import Wizard (Data -> From Text)
   
3. Select your .csv file and Import
   
4. In the dialogue box, choose 'Delimited', and click Next.
   
5. Choose your delimiters (uncheck everything but 'comma'), choose your Text qualifiers (likely {None}), click Next
   
6. In the Data preview field select the column you want to be text. It should highlight.
   
7. In the Column data format field, select 'Text'.
   
8. Click finished.
   

Solution 4:

You can simply format your range as Text.

Also here is a nice article on the number formats and how you can program them.

Solution 5:

Actually I discovered that, at least starting with Office 2003, you can save an Excel spreadsheet as an XML file. Thus, I can produce an XML file and when I double-click on it, it'll be opened in Excel. It provides the same level of control as SYLK, but XML syntax is more intuitive.