zip code

  • Thread starter Thread starter jillteresa
  • Start date Start date
J

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
:eek:
 
To dispaly as 5 digits format column as

00000

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

=TEXT(A1,"00000"
 
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).
 
WOW - I do not understand that formula at all but it worked perfectly..
thanks so much for the help!!!!
Jil
 
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
 
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
 
I found it...I should have a "<" sign there instead of ">"
KH

Karl H said:
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
Click to expand...
 
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
 
No, the ">" was right, but converting the "donor" data to Number, allowed
proper completion of the formula.
Karl
 
Back
Top