Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing ZIP code formats in Excel 2003
I am trying to merge addresses into a mail program that won't accept 9-digit
ZIP codes - some of the addresses in my worksheet are the acceptable 5-digit ZIPs, some are the unaccepted 9-digit ZIPs. How do I efficiently trim or shorten the cells that now have 9-digits, without manually going to each cell and deleting the last 4 digits? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing ZIP code formats in Excel 2003
Hi
A formula option could be: =LEFT(A1,5) copied down - assuming Zips are in the A column (replace as appropriate). This will leave your 5 digit Zips unchanged, but will trim off the last 4 characters from 9 digit zips. Hope this helps! Richard On 28 Jan, 18:32, cdbinder wrote: I am trying to merge addresses into a mail program that won't accept 9-digit ZIP codes - some of the addresses in my worksheet are the acceptable 5-digit ZIPs, some are the unaccepted 9-digit ZIPs. How do I efficiently trim or shorten the cells that now have 9-digits, without manually going to each cell and deleting the last 4 digits? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing ZIP code formats in Excel 2003
Use the Left(cell_ref, num_chars) Text function
"cdbinder" wrote: I am trying to merge addresses into a mail program that won't accept 9-digit ZIP codes - some of the addresses in my worksheet are the acceptable 5-digit ZIPs, some are the unaccepted 9-digit ZIPs. How do I efficiently trim or shorten the cells that now have 9-digits, without manually going to each cell and deleting the last 4 digits? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing ZIP code formats in Excel 2003
=LEFT(cellref,5)
Gord Dibben MS Excel MVP On Sun, 28 Jan 2007 10:32:01 -0800, cdbinder wrote: I am trying to merge addresses into a mail program that won't accept 9-digit ZIP codes - some of the addresses in my worksheet are the acceptable 5-digit ZIPs, some are the unaccepted 9-digit ZIPs. How do I efficiently trim or shorten the cells that now have 9-digits, without manually going to each cell and deleting the last 4 digits? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing ZIP code formats in Excel 2003
The formula works great: I inserted a new column next to the ZIP code
columns, used the LEFT formula and filled all the way down. Perfect! Now a new problem - when I merge all my data into this rather inflexible mail program I mentioned, the number of columns have to be exact, no extra columns. However, I cannot delete the old ZIP code column with the mismatched 5- and 9-digit ZIPS because it is now the reference point for the new perfectly formated, all-5-digit ZIPs. Any ideas? "RichardSchollar" wrote: Hi A formula option could be: =LEFT(A1,5) copied down - assuming Zips are in the A column (replace as appropriate). This will leave your 5 digit Zips unchanged, but will trim off the last 4 characters from 9 digit zips. Hope this helps! Richard On 28 Jan, 18:32, cdbinder wrote: I am trying to merge addresses into a mail program that won't accept 9-digit ZIP codes - some of the addresses in my worksheet are the acceptable 5-digit ZIPs, some are the unaccepted 9-digit ZIPs. How do I efficiently trim or shorten the cells that now have 9-digits, without manually going to each cell and deleting the last 4 digits? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing ZIP code formats in Excel 2003
Figured it out - create a 3rd column, copy just the values, not the
referenced formulas. Then delete the other 2 columns. Time consuming, but much less so than altering each offending 9-digit cell. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing ZIP code formats in Excel 2003
I expect you will be doing this again, and I would
suggest that you don't destroy the zip+4 codes if you use text you should be able to use both 5 digit zips, and 5+4 digit zips, and International zip codes, but if the required format for something is as you say 5 digits. The fields should still normally be text and not numbers because they are identifying information. Five digit codes is really only good for the US and Caribbean. You might want to take a look http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5 If you are doing Mail Merge you can work with text dealing with numbers can be difficult. If you are working with a column with a number format or General the 5 digit zip codes would be treated as numbers and the 9 digit codes entered with a hyphen would be accepted as text. -- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm -- --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "cdbinder" wrote in message ... Figured it out - create a 3rd column, copy just the values, not the referenced formulas. Then delete the other 2 columns. Time consuming, but much less so than altering each offending 9-digit cell. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 autosave a changing file | Excel Discussion (Misc queries) | |||
Opening Excel 2003 files in Excel 97 | Excel Discussion (Misc queries) | |||
Text, Number and Date formats excel 2003 driving experienced user | Excel Discussion (Misc queries) | |||
trying to open an excel file in excel 2003 | Excel Discussion (Misc queries) | |||
sharing/using/saving Excel 2002 files in Excel 2003 | Excel Discussion (Misc queries) |