Format Zip Code Bug

[Al Franz]

Using the latest version of Excel, 2003. If you have a column of zip codes,
some 5 digit and some 9 digit, and format them as 9 digit zip codes the
formatting tool is not smart enough to leave the 5 digit zip codes alone.
Instead it insert 4 leading 0's. Probably not how it should work.

 

[Dave Peterson]

Maybe you could use a custom format to cover both 5 digits and 9 digit zips:

[>99999]00000-0000;00000

 

[David McRitchie]

What really should be done is that all zip codes should be
text. I don't know what you have now. Formatting is not
going to change the value, nor is formatting alone likely
to change text to numbers or numbers to text.

Make a copy of the worksheet and follow directions and use
macro with fixuszip5
http://www.mvps.org/dmcritchie/excel/join.htm#fixUZzip5

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

Maybe you could use a custom format to cover both 5 digits and 9 digit zips:

[>99999]00000-0000;00000

Using the latest version of Excel, 2003. If you have a column of zip codes,
some 5 digit and some 9 digit, and format them as 9 digit zip codes the
formatting tool is not smart enough to leave the 5 digit zip codes alone.
Instead it insert 4 leading 0's. Probably not how it should work.