Excel Removes Leading Zeros From My Zip Code Data When I Export To .CSV How Do I Fix That?
If you have worked with Excel you may notice that any time it sees a number with a leading zero it will remove it from your cells. And when it comes to APN and Zip Codes this can be quite frustrating.
Not worry there is a solution…
In fact, Microsoft even has a great help article dedicated to this topic which you can find here:
https://support.office.com/en-us/article/Keep-leading-zeros-in-number-codes-1bf7b935-36e1-4985-842f-5dfa51f85fe7
I encourage you to read through that article as it is quite detailed.
But for a quick summary of it keep reading here…
Basically you need to let Excel know to treat your Zip Code or APN fields as Text not Numbers (even though they are numbers), sounds strange I know, but keep in mind Excel was designed for Accountants and number can also be treated as text in their world.
So how do we tell excel to treat our data column as text?
Here’s what you do…
- Assuming your Zip code field is in column A and your data starts in row 2, in a blank column enter in the function =TEXT(A2″00000″)What this does is tell excel that the number should be 5 digits long and that if it is not add leading zeros to make up for the missing digits.
- Copy your function down through all your rows.
- Copy the data in your new column and Paste Special/ Values Only into your zipcode column.
- You can delete now you other Zip Code column.
- Now highlight your Zip Code column and right-click on the header and select the ‘Format Cells’ option.
- From the ‘Category:’ selection menu select the ‘Text’ option and click ok.
- Now that you’ve got everything formatted properly you can go ahead and do a File>Save As .CSV and you will be saving the complete zip codes in there including those pesky leading zeros.
NOTE: Once you do the above steps the .csv file is saved correctly, however you will notice that when you open the newly saved file back up in Excel the leading zeros are missing again… why is that?
The reason they are missing is because a .csv file is a text file… when Excel opens that text file it applies it’s formatting to it and since formatting options are not saved in a .csv file Excel will again interpret the the zip codes as numbers again and will remove the leading zip code. But this does not change the fact that the .csv itself is saved correctly with the leading zeros.
You can verify that by opening the .csv file in a text editor like notepad instead of opening it in Excel. When you open it in a text editor you can see that the zip codes contain the leading zeros and life is good!
I hope this helps article helps you, but again for a more detailed look at this process it is best to always refer to the Excel user guide (when it comes to Excel related issues). Here’s that Microsoft article again if you want some more details on this process: https://support.office.com/en-us/article/Keep-leading-zeros-in-number-codes-1bf7b935-36e1-4985-842f-5dfa51f85fe7
