Zip Codes

[Guest]

Help...When I format a zip code file it appears in the field to show the "0"
at the beginning of the field but it does not show when I print. The only
work around is change it to text. I thought a zip code would recognize it as
a zero. any idears....

 

[JulieD]

Hi Dean

excel doesn't know what you've entered into a cell, it only recognises the
"type" of data (text, number, date etc) .. so it won't know that you've
entered zip codes and therefore won't "treat" them in a special way.

However, i'm interested in HOW you formatted the cells to show the leading
0, as when i do this using format / cells / custom ... and type in 00000 and
click OK
the leading zeros not only show up in the cell they show when i do print
preview ... so i'm not sure why your zeros show in the cell but not in print
preview.

Hope this helps
Cheers
JulieD

 

[Guest]

Julie,
We format it as a zip code they will show them but they then disappear at
print. If we format the cell as text and it will show in the cell and print.
Thanks Dean

 

[JulieD]

Hi Dean

how are you "formatting it as a zip code" ... what actual steps do you take?
(i'm in Australia and our postcodes are different to your zip codes so i'm
not actually sure what you mean when you say that you are formatting it as a
zip code).

what happens if you try the method i outlined in my last post - do you see
the 0 in print preview?

Cheers
JulieD

 

[Norman Jones]

Hi Julie,

how are you "formatting it as a zip code" ... what actual steps do you
take?

I would surmise:

Format | Cells | Special Zip Code

 

[Norman Jones]

Hi Julie,

Format | Cells | Special Zip Code

was intended to read:

Format | Cells | Special | Zip Code

 

[JulieD]

Hi Norman

thanks, makes more sense now - i don't have this option ..... must be a
"international/regional" type thing.

Cheers
JulieD

 

[Guest]

Julie,
We would right click on the cell or entire column and select "format
cells". We then select the "number" tab and can choose "special" or "text".
In the United States version when we select "special" we can the select "zip
code". Hope this helps.

 

[JulieD]

Hi Dean

sorry, i can't give you any more advice on this as i don't have the option
on my version to play around with ... can you use custom and set the format
up?

Cheers
JulieD

 

[Ron Rosenfeld]

Julie,
We would right click on the cell or entire column and select "format
cells". We then select the "number" tab and can choose "special" or "text".
In the United States version when we select "special" we can the select "zip
code". Hope this helps.

Which version of XL do you have?

In my version (Excel 2002 (10.6501.6714) SP3

(US Regional Settings)

I select A1, then right click and

Format Cells/Number/Special Zip Code <ok>

I then enter 1234 into A1.

The formula bar displays 1234
A1 displays 01234
Printing --> 01234


--ron

 

[David McRitchie]

Yes, but Julie in Australia (not US version of Excel)

the US zip format in Special is 0000
and the zip+4 format is 00000-0000

You can use the following
[<100000]00000_-_0_0_0_0;[>0]00000-0000;;@

Problem with anything but text ist that 12345-1234 is a
much larger number than 12345 and will not be sorted to
the same area.

To fix anything that was manually entered I have a macro
fixUSzip5 found at
http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5
so that 5 digits or less will be converted to text as 5 digits
and anything else would already be text if manually entered
into cells as General. So everything then is text and left
just justified, and sorts properly.