Software

Ask something
Question Locked

MERGE FROM EXCEL INTO A WORD DOCUMENT-FORMATTING PROBLEM

0 helpful
9 replies

by suzanne_juszcyk

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I'm using an Excel spreadsheet for data for a Word main document. I'm having trouble with numbers; if they are automatically formatted with the dollar sign and two decimal places, i.e., $1,200.00, the dollar sign, period and zeros at the end do not merge into my Word document when I merge them, just the number (1200) comes in. The only way it will work if I manually type them in and have the cell formatted to text. Why is this happening? And is there anyway to get it work? thanks

9 replies

by techbro

Those two zeros behing the decimal place do not really exist. Excel only shows them to you as a placeholder so that your numbers will all line up if you actually type in a number with characters after the decimal (like $123.78).

Mathematically speaking, depending on your desired precision, every integer has an infinite number of zeros after the decimal place.

Qustion: Are you using the numbers for calculation in Excel? If not you could just update all of your cells wih a simple formula like (=A2&".00") where A2 is the cell with the current number (like 1200). The result would be "1200.00"

Alternatively, you could rethink about how you are importing your data into Word. I beleive (not sure which version you are using) that if you Paste Special and select "Link" and "Formatted Text" you would still see the trailing zeros.

You could also try just doing a Paste Special without the link, but still choosing "Formatted Text."

Several options for you. Hope one works best for your needs.

by suzanne_juszcyk

Hi there, and thanks for your reply. I don't think paste special will work, as I am merging the data into Word into a new document, when would I use it? The dollar sign and zeros have to be in this Word document and since sometimes it will not be a number I can't put them into the main document. So I think my answer is it can't be done with that formatting in Excel. If not, let me know. I appreciate your help! thanks

by techbro

I didn't catch that you were doing a merge.

In Excel, if you format the cells as text and also make sure that you either type in the trailing zeros or use a quick formula to add them, then your formatting will hold up through the merge.

However, if ou need to do calculations on the data in Excel, then this will not work.

Hope that helps.

by techbro

What you need to do is add a formatting switch to the merge field in Word. Specifically, the switch that you need to add is:

\# "$,0.00;($,0.00)"

I put some screenhots in a Word document to walk you through the process. Email me if you want me to send the file to you.

by atcollege

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".

by michael.barnett

Thank you for your input. I've been searching the web for awhile trying to find something that would work concerning my formating problem during mail merge, and your instructions were great. Thanks.

by adurkee

This solution works (I used it in Word 2007), but I would like for my users to not have to select the DDE option all the time. Is there a way to set that conversion type as the default???

by atcollege

There is no way that I have noticed to make the DDE type of connection the default. Probably because the DDE type is a very old technology. But my users have been trained to look for the DDE and they are comfortable with it now

This discussion is closed to new replies.