Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FormatCellsSpecialZip 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 "FormatCellsSpecialZip 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FormatCellsSpecialZip Code
Your 5+4 ZIPs were presumably stored as text, rather than as numbers, so
formatting the cell has no effect. -- David Biddulph "MichaelRobert" wrote in message ... 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 "FormatCellsSpecialZip 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FormatCellsSpecialZip Code
David:
Thanks for the suggestion but I don't think that 'stored as text' is the problem. The FormatCellsSpecialZip 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 "David Biddulph" wrote: Your 5+4 ZIPs were presumably stored as text, rather than as numbers, so formatting the cell has no effect. -- David Biddulph "MichaelRobert" wrote in message ... 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 "FormatCellsSpecialZip 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FormatCellsSpecialZip Code
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). -- David Biddulph "MichaelRobert" wrote in message ... David: Thanks for the suggestion but I don't think that 'stored as text' is the problem. The FormatCellsSpecialZip 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 "David Biddulph" wrote: Your 5+4 ZIPs were presumably stored as text, rather than as numbers, so formatting the cell has no effect. -- David Biddulph "MichaelRobert" wrote in message ... 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 "FormatCellsSpecialZip 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FormatCellsSpecialZip Code
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 FormatCellsSpecialZip Code command does not convert the entry to 5 digits. Should it? Mike "David Biddulph" wrote: 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). -- David Biddulph "MichaelRobert" wrote in message ... David: Thanks for the suggestion but I don't think that 'stored as text' is the problem. The FormatCellsSpecialZip 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 "David Biddulph" wrote: Your 5+4 ZIPs were presumably stored as text, rather than as numbers, so formatting the cell has no effect. -- David Biddulph "MichaelRobert" wrote in message ... 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 "FormatCellsSpecialZip 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FormatCellsSpecialZip Code
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 MichaelRobert wrote: 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 FormatCellsSpecialZip Code command does not convert the entry to 5 digits. Should it? Mike "David Biddulph" wrote: 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). -- David Biddulph "MichaelRobert" wrote in message ... David: Thanks for the suggestion but I don't think that 'stored as text' is the problem. The FormatCellsSpecialZip 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 "David Biddulph" wrote: Your 5+4 ZIPs were presumably stored as text, rather than as numbers, so formatting the cell has no effect. -- David Biddulph "MichaelRobert" wrote in message ... 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 "FormatCellsSpecialZip 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FormatCellsSpecialZip Code
David and Beege:
What I am taking from this discussion is that the FormatCellsSpecialZip 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 "Beege" wrote: 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 MichaelRobert wrote: 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 FormatCellsSpecialZip Code command does not convert the entry to 5 digits. Should it? Mike "David Biddulph" wrote: 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). -- David Biddulph "MichaelRobert" wrote in message ... David: Thanks for the suggestion but I don't think that 'stored as text' is the problem. The FormatCellsSpecialZip 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 "David Biddulph" wrote: Your 5+4 ZIPs were presumably stored as text, rather than as numbers, so formatting the cell has no effect. -- David Biddulph "MichaelRobert" wrote in message ... 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 "FormatCellsSpecialZip 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to detect symbol/special character code | Excel Discussion (Misc queries) | |||
Format Cells by code | Excel Worksheet Functions | |||
Format cells for special characters | New Users to Excel | |||
Format cells for special characters | New Users to Excel | |||
Special Code | New Users to Excel |