Help with Excel formatting of Zip Codes

Thank you everyone I appreciate the help!! :blush:

There are certainly workarounds possible. For example, if you do as @Dewey_Finn suggested,

that would probably work, in the sense of preserving the leading zeroes, but those quotation marks might still be part of the Excel file, which may or may not be acceptable if it has to be in a specific format.

Right, the CSV has the correct data, but Excel messes it up when it opens it. This means that if the OP uses the CSV in the other customer software, it might be OK (depending on what the other software does with it). But if you ever need to edit the file again, you’ll have to make the same changes all over again.

When I have a similar need, I maintain the file in .xlsx format so the formatting is correct, and then export to CSV each time I need to use it elsewhere.

I just tried, and yes, the quotation marks are still present when I reloaded the document in Excel.

Okay, there is a workaround, although it’s probably no easier than just reformatting each time you open the file.

Format the ZIP codes as text strings, then they are saved with the leading zeros in the CSV file. Instead of opening the CSV file in Excel, use File \ Import. On the third screen of the import wizard you can change the import format from “General” to “Text”.

I also notice that if you have lost the leading zeroes and have the ZIP codes stored as numbers, using the special ZIP code format diplays the requisite leading zeroes automatically. If you must store only CSV files, the simplest thing is honestly just to leave everything as numbers, and re-format the column as ZIP codes each time you open the file in Excel.

Wow that’s great! Thank you!!

You could also create a new column next to the original one, and add the following formula (assuming zip code is ColA):

=TEXT(A2, “00000”)

Oh awesome! I will try that. Thank you!! :blush:

An even easier approach - just stop doing business in New England.

Hah! :smile:

Or, I suppose you could type in your zip codes using capital Os instead of zeroes.

=TEXT(A2, “00000”)


Who is the recipient of this file? You can specify the format of columns when opening/reading in a csv-file. And any program/person requiring a csv-file containing zip codes aught to be specifying it should be read in as text.

Love it! :grin: