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.