 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Format>Cells>Special>Zip Code
Every day I receive a list of Zip Codes covering Sales Leads. The as-received format includes regular 5 digit zips, 5+4 zips, and 5 digit zips with the leading zeros omitted, but I need to change it to 5 digit to process further. Routinely, I use the command "Format>Cells>Special>Zip Code" to add the leading zeros back. But the command seems unable to convert a '5+4' format to a simple 5 digit format, and I end up having to delete the hypen and the last 4 digits manually. Am I missing a trick? Mike Your 5+4 ZIPs were presumably stored as text, rather than as numbers, so formatting the cell has no effect. -- David Biddulph > Every day I receive a list of Zip Codes covering Sales Leads. The > as-received [quoted text clipped - 10 lines] > > Mike David: Thanks for the suggestion but I don't think that 'stored as text' is the problem. The Format>Cells>Special>Zip Code command has no trouble adding dropped leading zeroes. And it will not convert 5+4 ZIPs to 5 ZIPS after I have formatted to 'number'. Is there anyhting else? M > Your 5+4 ZIPs were presumably stored as text, rather than as numbers, so > formatting the cell has no effect. [quoted text clipped - 15 lines] > > > > Mike What do =ISTEXT(A2) and =ISNUMBER(A2) say for your 5+4 ZIPs? I would still suspect that you've got text rather than numbers. If you do have text, then =LEFT(A2,5) ought to work to convert them to 5 ZIPs. If they are numbers, then =LEFT(TEXT(A2,"000000000"),5) should do it (and on a quick test it looks as if that would probably work with the text ones too).  SignatureDavid Biddulph > David: > [quoted text clipped - 28 lines] >> > >> > Mike Thanks, David. ISTEXT returns FALSE; ISNUMBER returns TRUE. The LEFT command works well - thank you. Btw, even if I start with a fresh worksheet, and enter numbers to create a 5-4 ZIP (like 32034-1234) the Format>Cells>Special>Zip Code command does not convert the entry to 5 digits. Should it? Mike > What do =ISTEXT(A2) and =ISNUMBER(A2) say for your 5+4 ZIPs? I would still > suspect that you've got text rather than numbers. [quoted text clipped - 36 lines] > >> > > >> > Mike ISTEXT is going return TRUE to the 5-4 zip code. If you enter 00000-1111, Excel assumes its a text entry, otherwise there'd be confusion on whether it was a zip or a subtraction formula. Strings like 12345 are assumed to be numbers, unless you tell it otherwise, and you can format numbers to have leading and trailing zeroes, but you can't format text like that. My wish is that people would treat zip codes, telephone numbers, serial numbers (e.g part numbers) as they would text, because nobody is going to do any calculations to those, except as a text character string. Maybe clearer, maybe not. Hope it is. Beege > Thanks, David. > [quoted text clipped - 48 lines] >>>>> >>>>> Mike David and Beege: What I am taking from this discussion is that the Format>Cells>Special>Zip Code (5 digits) command will somehow change the format of 5 digits from ?? to a 5 digit Zip Code (ie no change at all except to add any missing digits as leading zeroes) . While the 5+4 command will change a 9 digit string into a 5-4 string. Is that right? If so, it hardly seems worth having a special command. Mike > ISTEXT is going return TRUE to the 5-4 zip code. If you enter > 00000-1111, Excel assumes its a text entry, otherwise there'd be [quoted text clipped - 63 lines] > >>>>> > >>>>> Mike |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.