zip codes starting with "0" in excel

[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?
..

 

[Paul]

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?

Please don't post to more than one newsgroup. See my answer in
worksheet.functions.

 

[Randall Roberts]

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

 

[Randall Roberts]

using your method EVERY zip code is going to have a 0 in front of it
so
90210 becomes 090210

the better solution is to simply format the cells as zip codes and
then the zeros will be shown

 

[Fred]

Depending on what you need to do with the data there may
be an easy work around.

In Excel 2000 there is a specific Zip # format (under
Special).

Or you could use Custom format "00000" to force numbers to
display as five digits.

Fred

 

[David McRitchie]

Hi Stacy,
Very wasteful of other people's time, but despite Paul warning us,
you've received several answers here as well, so here is a macro solution in
http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5