Zip code format

  • Thread starter Thread starter Nikki
  • Start date Start date
N

Nikki

I've got a list of about 1500 zip codes in my worksheet.
Unfortunately, they are not all formatted the same way. I
want them all to have the zip code plus 4 digits format
(00000-0000). But, whenever I choose that in the format
cells dialog box, it automatically changes the zip code
that were only 5 digits to have four leading zeros.
Example, from 11111 to 00001-1111, instead of 11111-0000.
I want it to add the zeros to the end, not the beginning.
Any idea on how I can get it to work this way? I figured
I'd be able to program it to work that way, but I'm not
familiar with the programming part, so I don't even know
where to start.

Oh, and I'm using Excel 2003...

Thanks!
 
Select the column then:
format => Cells => Number Tab, go to Custom

[<100000]00000;00000-0000

I believe this will do what you want.
 
I just tried that and it doesn't look like it did
anything...

Any other ideas?
-----Original Message-----
Select the column then:
format => Cells => Number Tab, go to Custom

[<100000]00000;00000-0000

I believe this will do what you want.
--
Regards,
Tom Ogilvy


I've got a list of about 1500 zip codes in my worksheet.
Unfortunately, they are not all formatted the same way. I
want them all to have the zip code plus 4 digits format
(00000-0000). But, whenever I choose that in the format
cells dialog box, it automatically changes the zip code
that were only 5 digits to have four leading zeros.
Example, from 11111 to 00001-1111, instead of 11111- 0000.
I want it to add the zeros to the end, not the beginning.
Any idea on how I can get it to work this way? I figured
I'd be able to program it to work that way, but I'm not
familiar with the programming part, so I don't even know
where to start.

Oh, and I'm using Excel 2003...

Thanks!
Click to expand...


.
Click to expand...
 
Well, it works fine for me. If that is your result, then it would appear
your zip codes are not being stored as numbers.

format a blank cell in a new sheet with the formatting I gave you and then
enter 11111 in the cell. For me it gives 11111-0000

--
Regards,
Tom Ogilvy


Nikki said:
I just tried that and it doesn't look like it did
anything...

Any other ideas?
-----Original Message-----
Select the column then:
format => Cells => Number Tab, go to Custom

[<100000]00000;00000-0000

I believe this will do what you want.
--
Regards,
Tom Ogilvy


I've got a list of about 1500 zip codes in my worksheet.
Unfortunately, they are not all formatted the same way. I
want them all to have the zip code plus 4 digits format
(00000-0000). But, whenever I choose that in the format
cells dialog box, it automatically changes the zip code
that were only 5 digits to have four leading zeros.
Example, from 11111 to 00001-1111, instead of 11111- 0000.
I want it to add the zeros to the end, not the beginning.
Any idea on how I can get it to work this way? I figured
I'd be able to program it to work that way, but I'm not
familiar with the programming part, so I don't even know
where to start.

Oh, and I'm using Excel 2003...

Thanks!
Click to expand...


.
Click to expand...
Click to expand...
 
Let me correct myself - I don't believe I correctly interpreted your
requirement - and I mispoke about 11111. Try this format

[<100000]00000-"0000";00000-0000

for 11111 it does display 11111-0000

--
Regards,
Tom Ogilvy


Nikki said:
I just tried that and it doesn't look like it did
anything...

Any other ideas?
-----Original Message-----
Select the column then:
format => Cells => Number Tab, go to Custom

[<100000]00000;00000-0000

I believe this will do what you want.
--
Regards,
Tom Ogilvy


I've got a list of about 1500 zip codes in my worksheet.
Unfortunately, they are not all formatted the same way. I
want them all to have the zip code plus 4 digits format
(00000-0000). But, whenever I choose that in the format
cells dialog box, it automatically changes the zip code
that were only 5 digits to have four leading zeros.
Example, from 11111 to 00001-1111, instead of 11111- 0000.
I want it to add the zeros to the end, not the beginning.
Any idea on how I can get it to work this way? I figured
I'd be able to program it to work that way, but I'm not
familiar with the programming part, so I don't even know
where to start.

Oh, and I'm using Excel 2003...

Thanks!
Click to expand...


.
Click to expand...
Click to expand...
 
That did it! Thank you so much!
-----Original Message-----
Let me correct myself - I don't believe I correctly interpreted your
requirement - and I mispoke about 11111. Try this format

[<100000]00000-"0000";00000-0000

for 11111 it does display 11111-0000

--
Regards,
Tom Ogilvy


I just tried that and it doesn't look like it did
anything...

Any other ideas?
-----Original Message-----
Select the column then:
format => Cells => Number Tab, go to Custom

[<100000]00000;00000-0000

I believe this will do what you want.
--
Regards,
Tom Ogilvy


I've got a list of about 1500 zip codes in my worksheet.
Unfortunately, they are not all formatted the same way. I
want them all to have the zip code plus 4 digits format
(00000-0000). But, whenever I choose that in the format
cells dialog box, it automatically changes the zip code
that were only 5 digits to have four leading zeros.
Example, from 11111 to 00001-1111, instead of 11111- 0000.
I want it to add the zeros to the end, not the beginning.
Any idea on how I can get it to work this way? I figured
I'd be able to program it to work that way, but I'm not
familiar with the programming part, so I don't even know
where to start.

Oh, and I'm using Excel 2003...

Thanks!


.
Click to expand...
Click to expand...


.
Click to expand...
 
Thank you so much, Tom! You don't know how much I
appreciate this! You saved me hours and hours of work.
Thanks again and I hope you have a great weekend!

Nikki
-----Original Message-----
Let me correct myself - I don't believe I correctly interpreted your
requirement - and I mispoke about 11111. Try this format

[<100000]00000-"0000";00000-0000

for 11111 it does display 11111-0000

--
Regards,
Tom Ogilvy


I just tried that and it doesn't look like it did
anything...

Any other ideas?
-----Original Message-----
Select the column then:
format => Cells => Number Tab, go to Custom

[<100000]00000;00000-0000

I believe this will do what you want.
--
Regards,
Tom Ogilvy


I've got a list of about 1500 zip codes in my worksheet.
Unfortunately, they are not all formatted the same way. I
want them all to have the zip code plus 4 digits format
(00000-0000). But, whenever I choose that in the format
cells dialog box, it automatically changes the zip code
that were only 5 digits to have four leading zeros.
Example, from 11111 to 00001-1111, instead of 11111- 0000.
I want it to add the zeros to the end, not the beginning.
Any idea on how I can get it to work this way? I figured
I'd be able to program it to work that way, but I'm not
familiar with the programming part, so I don't even know
where to start.

Oh, and I'm using Excel 2003...

Thanks!


.
Click to expand...
Click to expand...


.
Click to expand...
 
Thanks for the enthusiastic response and sorry for the initial "Bum Steer".

--
Regards,
Tom Ogilvy

Nikki said:
Thank you so much, Tom! You don't know how much I
appreciate this! You saved me hours and hours of work.
Thanks again and I hope you have a great weekend!

Nikki
-----Original Message-----
Let me correct myself - I don't believe I correctly interpreted your
requirement - and I mispoke about 11111. Try this format

[<100000]00000-"0000";00000-0000

for 11111 it does display 11111-0000

--
Regards,
Tom Ogilvy


I just tried that and it doesn't look like it did
anything...

Any other ideas?
-----Original Message-----
Select the column then:
format => Cells => Number Tab, go to Custom

[<100000]00000;00000-0000

I believe this will do what you want.
--
Regards,
Tom Ogilvy


message
I've got a list of about 1500 zip codes in my
worksheet.
Unfortunately, they are not all formatted the same
way. I
want them all to have the zip code plus 4 digits format
(00000-0000). But, whenever I choose that in the format
cells dialog box, it automatically changes the zip code
that were only 5 digits to have four leading zeros.
Example, from 11111 to 00001-1111, instead of 11111-
0000.
I want it to add the zeros to the end, not the
beginning.
Any idea on how I can get it to work this way? I
figured
I'd be able to program it to work that way, but I'm not
familiar with the programming part, so I don't even
know
where to start.

Oh, and I'm using Excel 2003...

Thanks!


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top