Recognizing "0" in zip codes

  • Thread starter Thread starter Ann
  • Start date Start date
A

Ann

I have set up an excel document with addresses and
formated one column as "zip code." Although the zip codes
appear fine in the column, when I view them in the formula
bar, if the zip code begins with a "0" (01742) it drops
the "0" (1742). This becomes a serious problem when I try
and use the excel document for addresses in a Word mail
merge. How can I get the document to recognize the "0" as
legitimate (other than by using a capital letter O
instead)?
 
Ann,

You can format the column for zip codes. Format - Cells - Number -
Special - Zip codes.
 
Could you not simply change the formating of the cell to a text format
Will that not then simply copy the entire string of charaters rathe
than recoginsing the cell or part of cell as a numeric value?

Just a thought!

Celtic_Avenge
 
Maybe try in a spare workbook ..

Assuming the zip codes are in col E, E2 down
Try in say F2: =TEXT(E2,"00000")
Copy F2 down

Kill the formulas in col F with an in-place:
copy > paste special > values > ok

Delete col E
(col F replaces)
 
One would think that would do it but it doesn't. I have
formatted the column as "zip code" and it still drops the
0. (I have Excel2003)

Ann
 
Ann,

Strange. If that's the case, they appear to be text, with only four digits.
Pick one, and select it. With none of the alignment buttons (left, center,
right on the Formatting Toolbar) selected, do your numbers appear at the
left of the cells, or the right.
 
Hi Ann,
I would suggest a macro solution to fix what you have:

Fix up for 5 digit US zip codes (#fixUSzip5)

US zipcodes are 5 digit or 9 digit (01234-1234) called zip+4. Only the 5 digit zipcodes are a problem because they get interpreted
as a number and get leading zeros stripped. The fixUSzip5 subroutine will repair the damage generally introduced by the Text to
Data wizard or by software converting a scanned image to an Excel file. Canadian zip codes are unaffected because they are not
numeric
Rearranging Data in Columns
http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5
 
Thanks everyone for responding to this query. The easiest
solution is to put an apostrophe in front of the zero in
the excel spread sheet ('01742). This will cause the merge
to recognize the zero but not reveal the apostrophe.

Thanks everyone for taking the time to reply.

Ann
 
Hi Ann,
I'd have thought the easiest way for future entries would be to
format the entire column(s) as text before you start entering text.
 
Back
Top