Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zip codes that begin with a zero...
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zip codes that begin with a zero...
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 -- Regards, Peo Sjoblom "Ofelia" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zip codes that begin with a zero...
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 -- Regards, Dave "Ofelia" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zip codes that begin with a zero...
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 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Ofelia" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Labels not printing zip codes | Excel Discussion (Misc queries) | |||
Counting codes | Excel Discussion (Misc queries) | |||
Changing codes | Excel Discussion (Misc queries) | |||
VBA Codes | Excel Worksheet Functions | |||
Excel doesn't sort zip codes properly | Excel Discussion (Misc queries) |