zip codes starting with a "0" in excel

  • Thread starter Thread starter stacy
  • Start date Start date
S

stacy

I have a list of 600 so addresses imported from somewhee
or another. Several hundred are from new england so the
zip codes, which start with a 0, are now displayed as 4
digit numbers.

Core Q: All new entries I've been typing ' before the 0 to
make the entry text, but how can I easily add the zero's
back to the other several hundred?

Stacy

I think these data were exported from contact software
originally in tab or comma delimited text file. HOw can I
avoid this zip problem in the future?
 
highlight the column for the zip codes

go to
format > Cells > select the number tab
Scroll down and select special then in the type box select zip code

now you will have all your Zeros back

Randall
 
stacy said:
I have a list of 600 so addresses imported from somewhee
or another. Several hundred are from new england so the
zip codes, which start with a 0, are now displayed as 4
digit numbers.

Core Q: All new entries I've been typing ' before the 0 to
make the entry text, but how can I easily add the zero's
back to the other several hundred?

Stacy

I think these data were exported from contact software
originally in tab or comma delimited text file. HOw can I
avoid this zip problem in the future?
Click to expand...

You could use a formula such as
="0"&A1
(copied down) to create the correct text, and then use Copy/Paste Values to
overwrite the originals.
 
You may also want to click on the column that contains the zip code and
them got format>cells. Goto the Number tab and scroll down to Special.
There is an option for Zip code and Zip+4. This will correct the
problem.
 
Hi Stacy,
Posting in multiple groups is very wasteful of other people's time.
You can pick a newsgroup based on what kind of an answer you
expect and if not sure simply post to excel.misc Most of us
read several newsgroups, and in any case there are always lots of
people capable of answering almost any Excel question regardless
of which group you post in.
http://www.mvps.org/dmcritchie/excel/xlnews.htm

My own suggestion is enter your zip codes as text. Would suggest
rather than using a numeric format to fix the US ones that you format
the column as text and then fix the US 5 digit codes to make them text.
The advantage then is that you are not limited to US zip codes for that
field, but can use any country's codes.

See macro solution in
http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5
 
Back
Top