leading 0 in zip code dropped

[con]

I have tried formatting for zip code, and for character,
and both ways the leading 0 in dropped. I need to import
the file into UPS shipping and UPS shipping will not
import if zip is not character and at least 5 characters.
Dropping the leading 0 makes the field 4 characters and
the zip is not correct. Help

 

[Norman Harker]

Hi Con!

Have you tried formatting as text?

If you have them as numbers using 00000 format at present you can use
a helper column to convert to text using:

=TEXT(A1,"00000")



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Guest]

When you use zip formatting, it does not store all 5 digits...it will display all 5, but if you copy to another cell, you will lose the leading 0s. You have to format the cells as text before you enter the zip codes. This will not add leading zeros if you are copying from another source

If you are copying from another source and need to get them back in 5 digit format
=IF(LEN(A1)<5,CONCATENATE(0,A1),A1) ...this adds 1 leading

- broo

----- con wrote: ----

I have tried formatting for zip code, and for character,
and both ways the leading 0 in dropped. I need to import
the file into UPS shipping and UPS shipping will not
import if zip is not character and at least 5 characters.
Dropping the leading 0 makes the field 4 characters and
the zip is not correct. Hel

 

[Roger R]

The other reply to this is correct, formatting it as text
should work. If you are typing the number in manually,
you can also type an ' before the first number in the zip
code and it will hold the zero in place, not as easy as
formatting as text, but it works!