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 |
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 |
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. |
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. |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 06:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com