Changing ZIP code formats in Excel 2003

[cdbinder]

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?

 

[RichardSchollar]

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

 

[headly]

Use the Left(cell_ref, num_chars) Text function

 

[Gord Dibben]

=LEFT(cellref,5)


Gord Dibben MS Excel MVP

 

[cdbinder]

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?

 

[cdbinder]

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.

 

[David McRitchie]

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