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



--
jillteresa
------------------------------------------------------------------------
jillteresa's Profile: http://www.excelforum.com/member.php...fo&userid=9498
View this thread: http://www.excelforum.com/showthread...hreadid=570026

To dispaly as 5 digits format column as

00000

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

=TEXT(A1,"00000")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=570026

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).


--
kraljb
------------------------------------------------------------------------
kraljb's Profile: http://www.excelforum.com/member.php...fo&userid=9955
View this thread: http://www.excelforum.com/showthread...hreadid=570026

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


--
jillteresa
------------------------------------------------------------------------
jillteresa's Profile: http://www.excelforum.com/member.php...fo&userid=9498
View this thread: http://www.excelforum.com/showthread...hreadid=570026

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

"daddylonglegs" wrote:


To dispaly as 5 digits format column as

00000

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

=TEXT(A1,"00000")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=570026

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

Karl H wrote:

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

"daddylonglegs" wrote:


To dispaly as 5 digits format column as

00000

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

=TEXT(A1,"00000")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=570026



--

Dave Peterson

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(H299999,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" wrote:

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

Karl H wrote:

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

"daddylonglegs" wrote:


To dispaly as 5 digits format column as

00000

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

=TEXT(A1,"00000")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=570026



--

Dave Peterson

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

"Karl H" wrote:

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(H299999,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" wrote:

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

Karl H wrote:

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

"daddylonglegs" wrote:


To dispaly as 5 digits format column as

00000

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

=TEXT(A1,"00000")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=570026



--

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


Karl H wrote:

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

"Karl H" wrote:

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(H299999,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" wrote:

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

Karl H wrote:

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

"daddylonglegs" wrote:


To dispaly as 5 digits format column as

00000

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

=TEXT(A1,"00000")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=570026



--

Dave Peterson


--

Dave Peterson

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

"Karl H" wrote:


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

"Karl H" wrote:

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(H299999,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" wrote:

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

Karl H wrote:

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

"daddylonglegs" wrote:


To dispaly as 5 digits format column as

00000

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

=TEXT(A1,"00000")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=570026



--

Dave Peterson

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

Karl H wrote:

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

"Karl H" wrote:


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

"Karl H" wrote:

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(H299999,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" wrote:

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

Karl H wrote:

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

"daddylonglegs" wrote:


To dispaly as 5 digits format column as

00000

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

=TEXT(A1,"00000")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=570026



--

Dave Peterson


--

Dave Peterson