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:
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.
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