Recognizing "0" in zip codes

[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)?

 

[Earl Kiosterud]

Ann,

You can format the column for zip codes. Format - Cells - Number -
Special - Zip codes.

 

[Celtic_Avenger]

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

 

[Max]

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)

 

[Ann]

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

 

[Earl Kiosterud]

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.

 

[Debra Dalgleish]

There's an article on the Microsoft web site that might help you:

Answer Box: Numbers don't merge right in Word
http://office.microsoft.com/en-ca/assistance/HA011164951033.aspx

 

[David McRitchie]

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

 

[Ann]

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

 

[David McRitchie]

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.