zip code

[jillteresa]

Hi there,
I have a file with zip codes. In this file, all zip codes that
normally begin with "0" are missing the zero. For instance, 06882 is
showing 6882. I need to add the zeros in somehow, quicky. Is there a
way to convert this data?
Jill

 

[daddylonglegs]

To dispaly as 5 digits format column as

00000

or use a formula in another column, e.g.

=TEXT(A1,"00000"

 

[kraljb]

Add a dummy row next to it and use =REPT("0",5-LEN(A1))&A1 Will make the
Zip Code text and add the preceding zeros (However, this will not work
if you have the nine-diget zip code).

 

[jillteresa]

WOW - I do not understand that formula at all but it worked perfectly..
thanks so much for the help!!!!
Jil

 

[Guest]

Hi,
How do you get that formula to work down a column of 100 zip codes witout
having to write:
=TEXT(A1,"00000")
=TEXT(A2,"00000")
=TEXT(A3,"00000") in each cell?
etc.
Thank you,
Karl

 

[Dave Peterson]

Put it in the topmost cell.

Then use the autofill button (at the bottom right corner of the selected cell)
and drag it down the column.

Debra Dalgleish has some pictures:
http://www.contextures.com/xlDataEntry01.html

 

[Guest]

Hi Dave,
I saw another post you made suggesting an input formula for cnsideration,
and from that, I tried to format an if/then formula like this:
=IF(H2>99999,TEXT(H2,"00000\-####"),TEXT(H2,"00000"))
put it still puts a zip code " 33123" in the format of 00003-3123

Should I be writing this differently?
Thanks,
Karl

 

[Guest]

I found it...I should have a "<" sign there instead of ">"
KH

Hi Dave,
I saw another post you made suggesting an input formula for cnsideration,
and from that, I tried to format an if/then formula like this:
=IF(H2>99999,TEXT(H2,"00000\-####"),TEXT(H2,"00000"))
put it still puts a zip code " 33123" in the format of 00003-3123

Should I be writing this differently?
Thanks,
Karl

 

[Dave Peterson]

I don't think so.

Well, unless you changed the "then" and "else" stuff around.

If h2 > 99999 (more than 5 digits),
then use the 9 digit zip code format
else use the 5 digit zip code format

 

[Guest]

No, the ">" was right, but converting the "donor" data to Number, allowed
proper completion of the formula.
Karl

 

[Dave Peterson]

Ahhhh. That makes more sense--especially with that leading space!