How to preserve leading and trailing zeroes in a .csv file

, ,
1

Like the title said, I’m generating a .csv file and opening it with Excel. However, Excel gets rid of leading and trailing zeroes by default. For instance, 001 → 1 or 10.00 → 10

I read somewhere that if I do =“001” then the zeroes will remain there but once I save the file again in Excel the =“” is gone and the next time I open the file I get 1 instead of 001.

Any suggestions?

Many Thanks!!!

2

Henry,

I do not know if your requirement scenario will allow it, but if you wanna preserve the leading zeros in an excel cell, you precede the number with a single quote, ie, if a cell contains a value '0002, it will show as 0002. Now if you save this file as a .csv and open it up in excel, the leading zeroes will be neglected, however, if you open it up as a text file, you will see the leading zeroes intact.

Plz explain your requirement… may be there is an alternative way of achieving what you want.

HTH, Rohit

3

Thanks for the input, Rohit

Actually as I found out about my requirement late yesterday, the user just need to open the .csv file in Excel, and be able to copy the contents inside and paste to another worksheet. So I think I just need to do =“001” to show him 001 on the worksheet.

Many Thanks,
Henry

4

Not sure how we could implement a macro but while opening a Excel worksheet we run a macro that converts all the cells into “text” rather than default “General”. So that all the data in .csv will be preserved as is instead of having =“” around the values.

5

Ramesh,

Saving the workbook as a csv will remove the macros. A csv is, in other words a flat file, that can only have data…

Rohit

6

Rohit,

I think Henry is looking to save the csv as an excel sheet and not the other way round.

But you are right. If we save the excel as csv it will remove macros.

Ramesh Kuruba.

7

Ramesh,

Uhmm correct me if I am wrong… so Henry has a .csv file, that he wants to open in Excel and be able to see the leading zeroes in numbers wherever they exist. You suggested that writing a macro in workbook_open will do the trick… but… how would you write a macro in a csv? :-/

  • R
8

The trick is not creating a macro in the csv, but using a macro to open the .csv. You can create a VBA Macro in a normal excel file. In the VBA code attached to the excel document, insert code to open the .csv, make the necessary changes, and save the .csv. The VBA Code is completely independent of the .CSV file.