format zip codes in Excel

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Excel General (archive) RSS
Next:  sort in vb  
Author Message
asSYSter
External


Since: Oct 03, 2005
Posts: 1



PostPosted: Mon Oct 03, 2005 10:30 am    Post subject: format zip codes in Excel
Archived from groups: microsoft>public>excel>misc (more info?)

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/community/en-us/default.mspx?mid=2c896774-034f-45c4-b1f8-0b6bfacd358b&dg=microsoft.public.excel.misc
Back to top
Gary''s Student
External


Since: Oct 01, 2005
Posts: 3403



PostPosted: Mon Oct 03, 2005 10:37 am    Post subject: RE: format zip codes in Excel [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

There is no problem. If a cell is formatted as a ZIP code, leading zeros are
retained.
--
Gary''s Student


"asSYSter" wrote:

> 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/community/en-us/default.mspx?mid=2c896774-034f-45c4-b1f8-0b6bfacd358b&dg=microsoft.public.excel.misc
Back to top
Dave O
External


Since: Feb 11, 2005
Posts: 693



PostPosted: Mon Oct 03, 2005 11:08 am    Post subject: Re: format zip codes in Excel [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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.
Back to top
Myrna Larson
External


Since: Jul 08, 2004
Posts: 163



PostPosted: Mon Oct 03, 2005 3:37 pm    Post subject: Re: format zip codes in Excel [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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

On 3 Oct 2005 07:08:00 -0700, "Dave O" wrote:

>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.
Back to top
Danno
External


Since: Jun 17, 2004
Posts: 27



PostPosted: Mon Oct 03, 2005 3:57 pm    Post subject: RE: format zip codes in Excel [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

I simply add an apostrophe before the zero.

"asSYSter" wrote:

> 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/community/en-us/default.mspx?mid=2c896774-034f-45c4-b1f8-0b6bfacd358b&dg=microsoft.public.excel.misc
Back to top
Karl H
External


Since: Jan 19, 2005
Posts: 3



PostPosted: Mon Sep 04, 2006 3:20 pm    Post subject: RE: format zip codes in Excel [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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
Back to top
Earl Kiosterud
External


Since: Jul 12, 2005
Posts: 997



PostPosted: Mon Sep 04, 2006 7:56 pm    Post subject: Re: format zip codes in Excel [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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.

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Karl H" wrote in message

>
> 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
Back to top
Karl H
External


Since: Jan 19, 2005
Posts: 3



PostPosted: Mon Sep 04, 2006 7:56 pm    Post subject: Re: format zip codes in Excel [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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

"Earl Kiosterud" wrote:

> 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.
>
> --
> Earl Kiosterud
> www.smokeylake.com
> -----------------------------------------------------------------------
> "Karl H" wrote in message
>
> >
> > 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
>
>
>
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Excel General (archive) All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Warning: fopen(): open_basedir restriction in effect. File(/home/adsense_reject.txt) is not within the allowed path(s): (/home/helploc:/usr/lib/php:/usr/local/lib/php:/tmp) in /home/helploc/public_html/Giga/GigaFunctions.php on line 1144

Warning: fopen(/home/adsense_reject.txt): failed to open stream: Operation not permitted in /home/helploc/public_html/Giga/GigaFunctions.php on line 1144

Warning: fwrite() expects parameter 1 to be resource, boolean given in /home/helploc/public_html/Giga/GigaFunctions.php on line 1145

Warning: fclose() expects parameter 1 to be resource, boolean given in /home/helploc/public_html/Giga/GigaFunctions.php on line 1146