Dawn B
Had this question 11
Question
Dawn B asked on
| 50674 views

Mail Merge in Word 2010 with Excel 2010; field formatting lost in Word

I have a client merging an Excel list into Word legal documents. Merge worked fine with Office 2003 but now, with Office 2010, the fields are not maintaining the formatting from Excel. For instance, an Excel field of 8.25% reads as .082500000 in Word. An amount of $1,234.56 is 1234.56 in Word, etc. Any ideas for a quick fix or solution?

 

Thanks in advance,

Dawn BB

Doug Robbins - Word MVP
Found this helpful 3
Answer
Doug Robbins - Word MVP replied on

Reply

Also, in his "Word Field Maths Tutorial" that can be downloaded from:

http://www.gmayor.com/downloads.htm#Third_party

fellow MVP Macropod shows how numbers may be rounded up or down in Word.

           
Hope this helps,
Doug Robbins - Word MVP
dkr@mvps.org
Doug Robbins - Word MVP
Found this helpful 2
Answer
Doug Robbins - Word MVP replied on

Reply

That is a stupid response.

The correct answer is to use the following field construction:

{ = { MERGEFIELD [Name of mergefield] } * 100 \# "0.00%" }

Hope this helps,
Doug Robbins - Word MVP
dkr@mvps.org
Doug Robbins - Word MVP
Found this helpful 1
Answer
Doug Robbins - Word MVP replied on

See "Formatting Word fields with switches" on fellow MVP Graham Mayor's website at
http://www.gmayor.com/formatting_word_fields.htm

           
Hope this helps,
Doug Robbins - Word MVP
dkr@mvps.org
Ruchi Bisht
Found this helpful 0
Ruchi Bisht replied on

Word does not have the round of function that is why  8.25% appears as  .082500000.\

 

Check the below article for Date, Phone Number, and Currency fields are merged incorrectly when you use an Access or Excel data source in Word.

 

http://support.microsoft.com/kb/304387

 

 


Ruchi Bisht
Doug Robbins - Word MVP
Found this helpful 2
Answer
Doug Robbins - Word MVP replied on

Reply

That is a stupid response.

The correct answer is to use the following field construction:

{ = { MERGEFIELD [Name of mergefield] } * 100 \# "0.00%" }

Hope this helps,
Doug Robbins - Word MVP
dkr@mvps.org
Doug Robbins - Word MVP
Found this helpful 3
Answer
Doug Robbins - Word MVP replied on

Reply

Also, in his "Word Field Maths Tutorial" that can be downloaded from:

http://www.gmayor.com/downloads.htm#Third_party

fellow MVP Macropod shows how numbers may be rounded up or down in Word.

           
Hope this helps,
Doug Robbins - Word MVP
dkr@mvps.org
BethRob
Found this helpful 0
BethRob replied on

Reply

I am having similar difficulties.

 

I have direct number entries on a spreadsheet where the value is entered as ###.## - only two entries after the decimal, formatted the same. When I merge the information into Word, the Merge coding is adding more decimal places and numbers. For example, what is entered in Excel says 2.05, but if you look at the value in the Merge, it's 2.049999999997.

Anyone have any ideas on why this is happening? It takes me a long time to go back through the merge and correct the numbers that never should have been there in the first place.

 

Thanks!

Doug Robbins - Word MVP
Found this helpful 0
Doug Robbins - Word MVP replied on

Reply

Mail merge uses the data as it is STORED in Excel, not as it is displayed in Excel, which is controlled by the Excel cell formatting.

To format the output of mail merge, it is necessary to add a formatting switch to the mergefield in the mail merge main document (before executing the merge)

All of the information that you will need to do this is contained in the article "Formatting Word fields with switches" on fellow MVP Graham Mayor's website at:

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

Hope this helps,
Doug Robbins - Word MVP
dkr@mvps.org
SusanMcKee
Found this helpful 0
SusanMcKee replied on

Reply

This did not work for me.  Either I am entering the field construction incorrect or something... PLEASE PLEASE HELP!!!!
McBink
Found this helpful 1
McBink replied on
I found that if you have the correct formatting in Excel, it actually comes into the document once you "finish and merge".  Prior to that, you will note specifically formatted fields will not mbe formatted.  Try it!  I was going nuts with a time format, but once I finally merged the document and said I wanted to edit individual letters, the time was formatted correctly. 
Doug Robbins - Word MVP
Found this helpful 0
Doug Robbins - Word MVP replied on

Reply

I found that if you have the correct formatting in Excel, it actually comes into the document once you "finish and merge".  Prior to that, you will note specifically formatted fields will not mbe formatted.  Try it!  I was going nuts with a time format, but once I finally merged the document and said I wanted to edit individual letters, the time was formatted correctly. 

You may just have been lucky as Excel does NOT store its data in the way that it is displayed by the cell formatting.  Or, you may have been linking to the file using the out-dated DDE method.
Hope this helps,
Doug Robbins - Word MVP
dkr@mvps.org
LJ2012FORMAT
Found this helpful 0
LJ2012FORMAT replied on

Reply

Hi Doug,

    I have tried and read your recommendations and it makes logical sense.  However, when I put it into the field code as follows, it says that there is a syntax error:

 

={MERGEFIELD [Name of Merge Field] }*100} \# "##%" }

 

Another thing is when I go to edit the field code, it changes it to be the following:

 

={MERGEFIELD [Name of Merge Field] }*100} \# "##%" } \* MERGEFORMAT

 

Can you please help with the syntax?  Last thing to note is that when I try to put in the curly paranthesis before the =, it doesn't allow me to click "OK" to accept.  Thanks so much for your help!

 

 

Previous Previous Page of 3 Next Next