How to Force Excel to Keep Leading Zeros When Opening a CSV File

If you frequently export data to csv files, I’m sure you are well aware of the problem when opening those files in Excel. Excel automatically chops off any leading zeros on numbers that start with a zero. This can be a problem for IDs or zip codes that start with a zero. This is a major pain in the ass, especially if you do not want your users to have to jump through hoops to solve the problem.┬áThe zeros are actually in the csv, Excel for some reason just hides them.

The below solution will solve the dropping of the leading zero problem. It does not require any extra steps by the user. In your csv file, simply wrap the data that contains the number with leading zeros like so:

“=””0000012345″””,Stiglitz,Hugo,123 Some Street, Some town,Some State
“=””0000054321″””,Soze,Keyser,123 Some Street, Some town,Some State
“=””0000045312″””,Christmas,Lloyd,123 Some Street, Some town,Some State

In the above example, all 4 leading zeros on each ID will be maintained in the excel file when it is opened.

Download file, open it and see the leading zeros are there.

Leave a Reply