Software

Ask something
Question Locked

Mail Merge Labels from Excel Spreadsheet

0 helpful
6 replies

by pscharf

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

My Excel spreadsheet has columns that contain GL account codes and dollar amounts. When I try the merge in Word 2003, the fields that contain the dollar amounts do not print. The GL account code from the previous column prints in it's place. Instead of 411558 $56.63, it prints 411558 411558.
When I try the merge in Word 2008, the dollar amounts print, but with about ten extra digits: 56.600000000003.
Can anyone explain how to fix this?

6 replies

by tmalo627

In 2003 version: Double check the columns you have selected for the merge. The problem sounds like it is coming from there.

In 2008 version: select the column that contains the dollar amounts, then format cells as currency. That should do it.

Hope this helps.

by pscharf

2003 - I figured out I had a problem with the column headings in my spreadsheet and fixed that. I changed the format on all the dollar amount columns to currency. Now the dollar amounts are printing on the labels, but I have the problem of lots of extra decimal places with some of the numbers, and it seems to be random. Example, one dollar amount prints 256.87, on the next label the amount from the same column prints 23.780000000000001. I don't see any way to format numbers within the mail merge, and it is not keeping the format designated in the spreadsheet. There must be a way to make this work!

by thumbsup2

We go through this about once a year when we have to reset file names in our templates. And, if we didn't write it down we wouldn't be able to easily figure out how to do it again. It's hidden, or at least not in a very logical place to put it.

First though, to explain what is happening. This behavior occurs because the data is displayed in its native, stored format in Access or Excel. To format merged data, you must format the merge fields in the main Document. If you format the data in the data source, the formatting is not retained when you merge the data into the main Document.

The 'feature', if you want to call it that, which allows the numbers to print correctly is called a numeric picture switch. You format your main Document during the merge process. At the point during your merge when you can see the main document and before the actual merge takes place.... do this:

(1) Right click the field which is being displayed as your long number, choose Edit Field.

(2) In the Field screen, click the "Field Codes" button at the bottom which reveals the advanced field properties on the right.

(3) You should see something in the box similar to this:

MERGEFIELD FieldName

(4) Place the cursor following the FieldName and press the space bar ONE time.

(5) Type the following after the space:
\# $#,###.00

Your final output should look something like this:

MERGEFIELD FieldName \# $#,###.00

Click OK to leave the field editor.

If it's possible for any amounts to be $10,000 or greater, use this for your picture switch:

\# $##,###.00

If it's possible for any amounts to be $100,000 or greater..... well, you get the picture. :D

(edit... why does this silly forum change the letter D on the word 'document' SOMETIMES and not others?)

by pscharf

The field code did the trick, and I never would have figured it out myself!
Just a note to anyone else who reads this- I used #$#,###.## because my currency amounts include cents. Unfortunately, if there is an even dollar amount, the zeros after the decimal do not print.
When I did the inital merge, I had 3 pages of labels that all printed. I saved and closed the merge. When I reopen and preview, all the labels are there, but when I print "all", only one page prints. How do I get it to print all the labels? When this is in everyday use, there will probably be 8-10 pages to print.
Also, is there a way to save applied filters? They are gone when I reopen the document.

by thumbsup2

The mask I gave you told it to round all numbers to two decimal places (cents) and always print the two decimal places even if the number is whole. Using the zero in the mask says these numbers are always present.

The # sign makes the numbers optional in that position. So, if there is no number in that position in the data, it prints a space.

You don't want to put the dollar sign between two # signs. It should always print to the left of all numbers. In the example you showed above, your numbers could never be more than 9,999.99

Use of the numeric picture switch is explained very good in the built in HELP.

Now, as to where your 3 pages went. When you saved the document, you saved the template, not the 3 documents created during the merge. You'll have to go through the merge process again to produce the 3 pages a second time. During the process, near the end, you can save the newly created document (merged 3 pages) as another file and it will not have the merged spreadsheet attached to it nor will it have the field codes. It will have the data from the spreadsheet formatted into labels.

This discussion is closed to new replies.