Mail merge using Excel and Word 1

[icsupt]

I have a Word document. I'm using Excel as the data file. Everything works EXCEPT the zip code. There are zip codes for NJ which means they start with a "0". Even though I format it, copy and paste special, it still comes over to the Word document with only 4 digits.

Is there a way to tell Word to put a "0" in front of the zip code, or does this have to be done some kind of way in Excel?

Thanks in advance.

 

[michbar]

In your Excel worksheet, select your zip codes. Go to format, cells, number. Click on Special, then click zip code. When you merge, the zeros should be present.

 

[DonKsr]

I have the same problem with the leading zero in a zip code getting removed by Word 2002 mail merge. I have the field in Excel 2002 formatted as category Special with type Zip Code. This must be very common on the east coast. Has someone come up with the solution to this problem?

 

[macropod]

Hi folks,

Number formats in Excel aren't necessarily preserved in mailmerges. To guarantee getting the correct number format in Word, it's often necessary to use an appropriate numeric picture switch.

To get the desire result, you'll need to edit the mergefield in the Word document. If you select the zip code field and press Shift-F9, you sould see something like:
{MERGEFIELD ZipCode}

For a standard 5-digit zip code, you could format the mergefield in Word as:
{MERGEFIELD ZipCode \# "00000"}
This will force Word to display a 5-digit number with as many leading 0s as may be required.

For the 9-digit zip codes you could use:
{MERGEFIELD ZipCode \# "00000'-'0000"}

If you have a mix of both, you could use:
{IF{MERGEFIELD ZipCode}> "99999" {MERGEFIELD ZipCode \# "00000'-'0000"} {MERGEFIELD ZipCode \# "00000"}}

Finally, if you need to convert all ZIP codes to ZIP + four format, you could use:
{IF{MERGEFIELD ZipCode}> "99999" {MERGEFIELD ZipCode \# "00000'-'0000"} "{MERGEFIELD ZipCode \# "00000"}-0000"}

Cheers
PS: Note that the field braces (ie '{}') in all of the above fields aren't they kind of braces you can simply type in - they have to be specially created. You can do this by pressing Ctrl-F9, which creates a pair of such braces.

 

[DonKsr]

Here's what I found - Hope this solves your problem.
Don K
==========================================================

Here is THE answer - the only right answer.  I promise.

http://w­ww.pserie.­psu.edu/co­mpcntr/gui­des/MailMe­rge-XP.pdf

Use Dynamic Data Exchange

Dynamic Data Exchange (DDE) is one method available in the Microsoft Windows® operating system for transferring data between applications. It uses shared memory to exchange the data. If you use other data transfer methods, you run into a problem: Data from the worksheet is transferred in the native format in which Excel stores it rather than with the formatting that you applied in the worksheet cells.

To choose the DDE data transfer method when you're connecting to an Excel worksheet during a mail merge, all you have to do is select one check box in Word:

1. On the Word Tools menu, click Options, and then click the General tab.

2. Click Confirm conversion at Open.

At the step in the mail-merge process when you connect to your data file, after you locate the Excel worksheet you want to connect to, the Confirm Data Source dialog box opens. Click MS Excel Worksheets via DDE (*.xls), and then click OK. In the Microsoft Excel dialog box, for Named or cell range, select the cell range or worksheet that contains the information you want to merge, and then click OK. The numbers from the Excel spreadsheet will now look exactly the same in your merged documents as they look in the worksheet cells.

Note: It's probably a good idea to turn off the Confirm conversion at Open option after you finish your mail merge. Otherwise, you might be prompted to confirm your data source at times when that's the last thing you want to worry about.