Question

Locked

MERGE FROM EXCEL INTO A WORD DOCUMENT-FORMATTING PROBLEM

By suzanne_juszcyk ·
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

This conversation is currently closed to new comments.

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

All Answers

Collapse -

Trailing zeros don't really exist

by TechBro In reply to MERGE FROM EXCEL INTO A W ...

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.

Collapse -

I don't think paste special will work

by suzanne_juszcyk In reply to Trailing zeros don't real ...

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

Collapse -

Make the Excel cells "text"

by TechBro In reply to I don't think paste speci ...

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.

Collapse -

OK, I found the answer

by TechBro In reply to I don't think paste speci ...

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.

Collapse -

Display Formatted #'s from Excel in a Word Mail Merge

by AtCollege In reply to MERGE FROM EXCEL INTO A W ...

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

Collapse -

Worked Well

by michael.barnett In reply to Display Formatted #'s fro ...

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.

Collapse -

Can this solution be made permenant?

by adurkee In reply to Display Formatted #'s fro ...

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???

Collapse -

Make DDE the default

by AtCollege In reply to Can this solution be made ...

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

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

Software Forums