Learn IT

Excel currency values don't merge to Word properly

Question: How can one merge excel currency cell into Word letter mailing to print correctly?  All things that I have tried prints the amount as15897 instead of $15,897. I just wondered if you have a quick fix so I don’t have to go in manually and fix all of the amounts.

Answer: I have a fix, but it isn’t a real quick fix, but it is much faster than manually entering  hundreds of values. And once you have it set up in your Word options, you just have to remember to select the correct conversion for all formatting to convert properly. Follow the steps below for Word 2007/2010.

  1. The most important step is to change your Word options (File > Options) Click Advanced and in the General settings place a check mark on “Confirm File Format conversion on Open” and click OK.
  2. You must re-create your merge file (I prefer to use the Mail Merge Wizard pane under Mailings > Start Mail Merge).
  3. Indicate your document type and click Next. of and browse for your Excel file to select.
  4. You will be prompted with a screen “Confirm Data Source” with OLE DB database files. Do NOT select this. Instead, check Show All, which will display several data sources.
  5. Scroll to select MS Excel Worksheets via DDE (*.xls) and the entire spreadsheet or a specific spreadsheet if the workbook contains more than one.
  6. You’ll see your columns display (not necessarily in the same order as it was in Excel) and the currency format (as well as other cell formatting) should be retained.
  7. Continue your mail merge.

For help with general mail merge steps, go to Microsoft free online training. http://office.microsoft.com/en-us/word-help/mail-merge-i-use-mail-merge-for-mass-mailings-RZ010277841.aspx?CTT=5&origin=HA010215566

 

Leave a Reply