Home |
Search |
Today's Posts |
#1
|
|||
|
|||
format zip codes in Excel
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/comm...lic.excel.misc |
#2
|
|||
|
|||
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/comm...lic.excel.misc |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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/comm...lic.excel.misc |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
format zip codes in Excel
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
format zip codes in Excel
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
format zip codes in Excel
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Imported Date & Time format with calcs. managed in excel from imrp | Excel Worksheet Functions | |||
How can I get excel to mail merge zip codes plus 4 correctly? | Excel Worksheet Functions | |||
Excel enters date as a text format | Excel Discussion (Misc queries) | |||
Excel doesn't sort zip codes properly | Excel Discussion (Misc queries) |