Excel and Office RAQ

Overcome Excel to Word mailmerge number format problems

   

Tactical solutions

Research projects

Microsoft Office

Multiple choice guessing

Services

Knowledge measurement audit

Item bank services

Item design course

Products

WaterMarker

iOTA

STASiS

ACiS

CaSelector

CaSCADE

Technical articles

Excel and Office RAQ

Free Excel Add-in

Free PowerPoint Add-in

Contact

Home page

Q: I have been using Excel and Word to mailmerge data for years and yet have been completely stopped in my tracks by the problems I have had in my present task. I'm using Office 2002.

I have a large amount of data, some text, some currency, some dates, some general number data stuff.  My problems are as follows:

1.  Some of the dates (formatted as the equivalent of "d mmmm yyyy" fields) import from Excel into Word quite successfully.  Others import as the date value codes (eg 58947 or whatever).  Why?  I have tried retyping, overtyping etc and the only way I can seem to fix it is by formatting the cells as plain text, which then means I can't sort on them by date order.

2.  Some of the currency fields which are set to GB sterling with currency symbol in Excel, 2 decimal places, then import into Word with lots of extra decimal places which may or may not be un-rounded.  It means that each time I want to check my figures I have to go back into a large Excel sheet and see whether I have got 34,950.95 or 34,950.94699999!  Again, why is this and what can I do to fix it? 

3.  Similar problem to (2) - percentages seem to gain an extra shedload of decimal places, which also means I have to go back and check the original data. 

4.  Some of the currency fields import across into Word with the currency symbol, others do not.  *scratches head to work this one out* 

The frustration factor in this is huge as this data is used to provide correspondence which has to meet a legal compliance test.  We cannot - absolutely never, not ever, no way - have any of the info incorrect.

All suggestions gratefully received.  I am rapidly losing my sense of humour with it!

A: Can't help on WHY this is happening*, but I've seen it lots and I know how to fix it.

What you need to do is to add format switches to the MAILMERGE fields in the Word document. Let's say you have a field called 'Cost' which leaves Excel as 19.99 or similar, but turns up in Word as 19.991234. Here's how to fix it.

In the word document, start by keying Alt+F9 to expose all the field codes. Look for the one that says:

          {MERGEFIELD Cost }

There may or may not be quotes around the word 'Cost' - it doesn't matter either way. Put your cursor between the end of the word 'Cost' (after the quotes if there are any) and before the closing curly bracket and type the following:

          \# ##.00

The full field should now read:

          {MERGEFIELD Cost \# ##.00}

This next bit is IMPORTANT. Do a CRTL+A and then a F9 to update all fields. Now do Alt+F9 to switch back to field values instead of codes.

That should sort it.

There are a whole range of field switches like this. Word help has very good coverage - look for 'time and date pictures' or 'number pictures' or if all else fails, send Clippy looking for 'field switches' and you'll find it from there.

 

* Post-publication comments from Doug Klippert suggests that this is due to the use of OLE DB rather then DDE. Read more about this at his site here.

 

 

Back to other RAQ topics