I have been reformatting a column in my Excel spreadsheet to be for zip codes (5 digits) I have done it as a custom format, a special format, a text format and a general format but none of them stay in place once I save the file and close it. When I re-open the file it is back to a 4 digit number (my zero in the beginning is removed). This is so frustrating! Does anyone know how to correct this so it will not automatically change my formatting?? Any help would be greatly appreciated. 
Have you tried formatting the column as Zip Codes? It’s an option under Special.
See:
Yes and that works but the minute I save and close out it changes back. I re-open my file and it’s back to 4 digits.
If you add a single-tick quote mark ahead of a string of numbers, Excel will consider it to be text rather than a number, and won’t chop off the leading zero. You don’t use ZIP codes for math, so it hurts nothing for Excel to consider it a text string rather than a number. The single-tick quote won’t display onscreen or in print.
Weird. I’m running Excel 2013 (so far from the current version), and I created a blank sheet with column A set to Zip Code format. I entered a half dozen Zip codes, including some with a leading zero, saved the document, reopened it and the zip codes were intact.
Is there some restriction on this particular document? Can you use the Zip Code format on a new, blank sheet?
I was not aware of that option. I’ve been formatting the column as Text. Thank you!
Do you need it to be specifically a number format? I’d just put a " ’ " in front of it and force it to be a text value and go from there. It’ll still sort appropriately.
This does not sound like a formatting problem, it sounds like a problem with what’s happening when with saving/opening your file. Try making some other minor change (just enter a number in a cell), save and retrieve the file to make sure that your file is actually being saved and retrieved correctly.
I just tried this and as long as I keep it as an Excel file it will stay formatted correctly. But when I save it as a CSV file it reverts back to the 4 digit format. So it definitely has something to do with the CSV version.
That’s something you might have shared earlier. Is there a reason you need to export a CSV file?
Oh, well that explains it. A CSV file doesn’t contain/save any special formatting: it’s just saved in the form of text separated by commas.
The clue is in the name: comma-separated values. A CSV file is literally a string of numbers separated by commas, nothing else.
What happens if you put quotation marks around the Zip Codes before exporting to CSV?
Can it be text, rather than numeric values? If so, that should allow you to format the numbers as text strings in Excel, and retain leading zeroes when exporting to CSV.
Yes in order to upload the file to our customer software it has to be in CSV format. Our software will not accept Excel.
The numbers are saved in text form (so, for example, the number 42 would be saved as the character 4 followed by the character 2).
It’s up to Excel what format to interpret that as or convert to when it reads the file back in. I don’t know the details of how it does that.
So if I have it as text it means it will not save that first zero?
To answer my own question, this doesn’t appear to work. A CSV file can contain text strings, but anything that looks like a number appears to be stored as a number. If I create the string “00001” in Excel and export it as a CSV file, it gets stored as the number 1.
ETA: correction - it is stored in the CSV file as 00001, but when Excel retrieves it, it treats it as the number 1.
Ok I am new to using CSV format. I have always strictly used Excel. Thank you so much for your help!