I'm not sure why you want the zip codes to be pure numbers (instead of text)
as I doubt you will be doing any calculations with them; but, given that is
your set up, if you want to mix 5-digit and 9-digit zip codes where your
cell values are pure numbers, then use this Custom Format (instead of the
Special Zip+4)...

[99999]00000-0000;00000

and in your formulas, where you refer to a zip code cell (assumed to be A1
for this example), use this in place of that cell reference...

TEXT(A1,"[99999]00000-0000;00000")

--
Rick (MVP - Excel)


"gh" wrote in message
...
I have a column that has the zip codes in the cells. Some are 5 digit and
some are 9 digit, without the - in them. They were a number stored as text
and I changed them to a number. I formatted them using the format
selection from the popup menu. I used special|zip code + 4. When I use a
formula to put the address, city, state and zip together in another column
the - is missing from the zip code (546789087). Also the 5 digit codes are
not formatted correctly. Is there a formula that will check the code to be
5 or 9 and add the - to 9 digit code? What would cause the formatted code
not to copy?

TIA