Mail merge in Word 2007 using Excel data doesn't keep formatting.

By rbidels ·
I have a contact list as an Excel worksheet. The "Zip Code" column is formatted for "Zip+4). Looks good in Excel.

When I create a mail merge document in Word, the zip code is not formatted, (no hyphen.) I actually tried the same thing with a different file in Word 2003 and had the same problem.

Any ideas?


This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Found the answer.

by rbidels In reply to Mail merge in Word 2007 u ...

After perusing past posts I found one by "AtCollege" which pointed to the answer.

I'll paste it below. Thanks AtCollege.


Display Formatted #'s from Excel in a Word Mail Merge
The solution starts in Word. Go to Tools, Options, General tab. Check Confirm Conversion at Open. OK.
This is a permanent change in Word and you should only have to do it once.

In Excel, format dates and numbers the way you want them to appear in Word.

Back in Word's Mail Merge Wizard, Browse for the Excel recipient list. When it is opened, you will be prompted for the type of connection. Look for something with DDE in it. Once you select that, you will have to say what sheet, etc. to use. But then any fields which contain dates, numbers will display the formatting you chose in Excel.

This "problem" started in Word XP, I think. Microsoft has a knowledge base article about it. Word 2007 also has the same "feature".
Posted: 06/20/2007 @ 10:11 AM (PDT)

AtCollege 1
Job Role: Technical/PC Support
Location: Elizabethtown, PA
Member since: 08/03/2004

Collapse -

Confirm conversion on Open in Word 2007 not working

by AtCollege In reply to Found the answer.

Word 2003's Confirm Conversion on Open worked well. Number formats from Excel came across during Word Mail Merge.
Since WD2007 has the same kind of setting (Office Button, Word Options, Advanced, General section, check Confirm file format conversion on open)I thought it would work the same.
But I am finding this is not so. When connecting to the recipient list, there is only one way to connect - ODBC. Without the DDE selection, formatting doesn't come across from Excel.
I can supply field switches, but this doen't give me the right formatting. What should be 50% displays as 01%.
My guess is that if I can get DDE as a choice when connecting to the data source, that the formatting will come across from Excel.

Collapse -

FIX - floating point calculation

by AtCollege In reply to Confirm conversion on Ope ...

I applied the floating point calculation fix, a newer one and it fixed the problem. Here is what I used:
Now my formats come across from Excel just fine.

However Word 2007 makes getting the DDE type of connection harder to find. In the Confirm Data Source window, you have to check Show All, then scroll down near the bottom of the list to MS Excel Worksheets via DDE (*.xls)
Yes, it will work with xlsx files too.

Collapse -

DDE connection

by Melissasiobhan In reply to FIX - floating point calc ...

For some reason when I am setting up the DDE connection as I used to with Office 2003 it keeps giving me an error message that it can not establish the DDE connection. Anyone have an idea?

Collapse -

DDE Connections

by jimtanski In reply to DDE connection

I am experiencing the same problem and it is making me crazy! Did you learn anything because I did not see any comments. ANY help would be appreciated!

Collapse -

Export as CSV file

by AshishS In reply to Mail merge in Word 2007 u ...

I had the same issue but with dollar amount during mail merge from Excel to Word. After I saved the file as a CSV file and then pointed the mail merge to that, it got fixed.

Worth a shot.

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums