Converting 5 digit zips to zip +4

[Lynda Meyer]

Anyone know a quick way to convert 5 digit zip codes to
the format 12345-0000?

I have a spreadsheet with both zip+4 and 5 digit zip
codes...i need to format the cells as zip+4 in order to
import into a mail merge correctly.

Thanks.

 

[Frank Kabel]

Hi
either apply a custom format like
"12345-"0000

or use TEXT for this

 

[David McRitchie]

Hi Lynda,
Use text rather than creating what I'm pretty sure would be
invalid numbers. I think that would be for the post office
itself (i.e. Postmaster) and not P.O. Boxes either.

Aren't your zip+4 numbers text. To convert your 5 digit
numbers to text so that you don't lose leading zeros you can
use macro FixUSzip5 in
http://www.mvps.org/dmcritchie/excel/join.htm

I have a page on Mail Merge specifically relating to label
and use of Excel as the data base.
http://www.mvps.org/dmcritchie/excel/mailmerg.htm

 

[Ron Rosenfeld]

Anyone know a quick way to convert 5 digit zip codes to
the format 12345-0000?

I have a spreadsheet with both zip+4 and 5 digit zip
codes...i need to format the cells as zip+4 in order to
import into a mail merge correctly.

Thanks.

Well, not being certain how your mail merge will deal with leading 0's in a zip
code field, you could use either this TEXT function:

=TEXT(F1,"[<100000]00000""-0000"";00000-0000")

or the custom format
[<100000]00000"-0000";00000-0000


--ron