Zip+4 not converting from Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Janetjay - 24 Dec 2003 21:36 GMT

I have addresses in Excel and the Postal Code field/column was formatted using the "special, zip+4" option. In the Excel file, all the 5 digit zips have four (4) zeros in front and they merge into Word as 5 digits. The nine digit zips appear fine in Excel, but when they are merged into Word only a zero (0) shows up on the label.

This is frustrating as more and more post offices are requiring zip+4-- cna anyone help??

Reply to this Message

Doug Robbins - Word MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS - 25 Dec 2003 01:49 GMT

Hi Janet,

See the Formatting Word Fields article on fellow MVP, Graham Mayor's website
at

http://www.gmayor.com/formatting_word_fields.htm

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

> I have addresses in Excel and the Postal Code field/column was formatted using the "special, zip+4" option. In the Excel file, all the 5 digit zips

have four (4) zeros in front and they merge into Word as 5 digits. The
nine digit zips appear fine in Excel, but when they are merged into Word
only a zero (0) shows up on the label.

> This is frustrating as more and more post offices are requiring zip+4-- cna anyone help??

Reply to this Message

Janetjay - 26 Dec 2003 13:41 GMT

I don't understand switches or how to apply one-- the help in Word expains switches, but does not demonstrate how to apply them. I have gone so far as to "import" the data base into Word to try and adjust the ZIP field within in Word and the data is still imported from Excel showing zero "0" for the nine digit Zips

To me this seems to be a problem that Microsoft needs to fix instead of using an elaborate "end around/" to solve the problem, which at this point is still unsolved other than manual edditing of my labels.

Reply to this Message

The Findog - 26 Dec 2003 15:01 GMT

Try this- In Word, go to options and on the GENERAL tab check the "CONFIRM CONVERSION AT OPEN" box. When you choose your source, in your case Excel, choose the "MS EXCEL WORKSHETS VIA DDE (*.XLS)". This option apparantly treats the import a little different than the default value Word uses when the "CONFIRM CONVERSION" is not checked.

NOW, prior to completing your merge, open your Excel Spreadsheet. Formatting of that column may be crucial. If you are like most, this zip+4 is new and all the zips you entered are 5 digit with the exception of the 9 digit zips you are having trouble with. IF you SPECIAL format the column using the "Zip Code +4" option , then "0000" are added in front of the 5 digits, and the 9 digit zips appear unchanged. If you save the file and complete the merge in Word, then all the 5 digit zips you have come out like this: 63447 = 00006-3347. I'm not sure if the postal service will like this format.

SOLUTION-- close Word and re-open the Excel file. Re-format the ZIP column using the SPECIAL format of just "Zip Code". The "0000" that appear with the 5 digit zips are now gone and the 9 digit zips are basically unchanged. Complete your merge and it should come out like you had hoped.

Hope this works for you--FINDOG

Reply to this Message

Graham Mayor - 26 Dec 2003 15:52 GMT

Display the field content (ALT+F9 or right click on the field and toggle)
and you will see a field construction similar to the examples on my web page
that Doug directed you to. Add the switches as shown, toggle the display
back again to show the result, then F9 to update to reflect the change.

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>
Graham Mayor - Word MVP
E-mail gmayor@mvps.org
Web site www.gmayor.com
Word MVP web site www.mvps.org/word
<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>

> I don't understand switches or how to apply one-- the help in Word
> expains switches, but does not demonstrate how to apply them. I
[quoted text clipped - 5 lines]
> of using an elaborate "end around/" to solve the problem, which at
> this point is still unsolved other than manual edditing of my labels.

Reply to this Message