format zip codes in Excel

[Guest]

When entering data in an Excel spreadsheet, any zip codes that begin with
zero, such as mine "03301" drop the leading zero. Why can't one of the
formatting options be a zip code format that deals with the fact that you
need to change the format to text (which doesn't always work)?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...8-0b6bfacd358b&dg=microsoft.public.excel.misc

 

[Guest]

There is no problem. If a cell is formatted as a ZIP code, leading zeros are
retained.

 

[Dave O]

You can create a custom format by right-clicking the appropriate cell,
then select >Format Cells and select the Number tab along the top.
Select Custom from the list on the left. In the Type: box (which shows
the current format of the cell) type ##### or #####-#### if you want
zip+4 . Click OK and you're done.

 

[Myrna Larson]

Hmmm... In Excel 2003, under Format/Cells/Number/Special, I see Zip code and
Zip+4. Seems to me they have been there for several versions now <g>.

 

[Guest]

I simply add an apostrophe before the zero.

 

[Guest]

One problem I had after exporting Access zip code as text to an Excel file
was being unable to have the Zip Code format go to Excel.

I solved the problem by highlighting the Zip Code fields/Format cells,
selected number, and the diagonal yellow warning sign gave the option of
"number stored as text" or "convert to Number." I chose, "Convert to Number."

Only after converting to "Number" would the data format correctly by
selecting: Format Cells/Special category/Zip + 4

 

[Earl Kiosterud]

Karl,

This is normal. This gets to the data "type," which determines how stuff is
stored. Unlike Excel, which is downright anal about text vs number data
types for storing in cells, Access often uses the text type to store
numbers, unless specific number formatting is needed (currency, etc), and
will still perform arithmetic operations on them. Excel usually won't.
Your zip code field in Access was likely set up as the text type, and so
came into Excel as text, on which Excel's number formatting has no effect.
By converting them to the number type, the Excel number formats could take
control if the presentation of the numbers.

 

[Guest]

Hi Earl,
I did discover that worked, but then I'm bringing that data back into a
publisher file for print-merge & publisher doesn't retain the formatting, for
some reason.
Very frustrating,
Karl