Zip codes that begin with a zero...

[Ofelia]

I have a zip code that begins with a zero. I try to format the cell and it
appears to work, but it does not show the zero in the formula bar. Then I'll
save it but when I reopen the worksheet it does not save it. Is there some
other way of doing this? Please Help!- Thanks

 

[Peo Sjoblom]

If you want a leading zero not only for display but for some other reason
you need to format as text or precede entries with an apostrophe '
otherwise use the zip code format under formatting

 

[David Billigmeier]

How are you saving the file? Is it a .csv? If so, the zero's should be
saved. When you open a .csv in excel it automatically strips the zero's from
VIEW, but they are still on the file. Try opening with a text editor
(notepad), and you will see the zero's.

If you are saving as an excel sheet (.xls), the formatting should be saved
as well.

If you want to hard code the 0's to the front of your zip codes, use the
following formula in a helper column (assuming your data starts in cell A1),
and then do a "Paste Special"->Values on the range to get rid of the formula.

=REPT("0",5-LEN(A1))&A1

 

[David McRitchie]

Since you fix them, save them, and lose them upon reopening, it
sounds like you are not opening and saving an Excel file as one of the
repliers mentioned. File should be .xls use File, save or
if it is not .xls then file, save as.

If you want to allow a mixture of 5 digit and 9 digit US zip codes,
you would be best formatting the column as text before entry.
Also as text you can use zip codes from any country, and more
important for those US only zip codes -- you can sort them.

After formatting as text which would only help with new entries
you can fix your existing entries with a macro. see
Fix up for 5 digit US zip codes (#fixUSzip5)
http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5