# Word Mail Merge Problem - is it a bug?



## DebbyG (Feb 3, 2009)

I have an excel spreadsheet which I am running a mail merge from.  All fields are setup in the correct format, i.e. western date & currency

When I run the mail merge in Word, it pulls through the date format in US, i.e. mm/dd/yy  rather than dd/mm/yy. 

It is also pulling through the value field as numbers and not currency. 

I heard talk this could be a bug, but would anyone out there know about how to fix this problem please as it's causing me a major headache.

Very many thanks


----------



## Macropod (Feb 3, 2009)

Hi Debbie,

It's not a bug - it's just the way some mailmerge connection types work. Here's a series of simple fixes:

*Mailmerge Number & Currency Formatting*
In Word, add a numeric picture switch to the mergefield. To do this:
. select the field;
. press Ctrl-F9 to reveal the field coding. It should look something like {MERGEFIELD MyData};
. edit the field so that you get {MERGEFIELD MyData \# $,0.00} (or whatever other numeric format you prefer - see below);
. position the cursor anywhere in this field and press F9 to update it;
. run your mailmerge.

Note: The '\# $0.00' in the field is referred to as a numeric picture switch. Other possibilities include:
. \# 0 for rounded whole numbers
. \# ,0 for rounded whole numbers with a thousands separator
. \# ,0.00 for numbers accurate to two decimal places, with a thousands separator
. \# $,0 for rounded whole dollars with a thousands separator
. \# $,0.00;($,0.00);- for currency, with brackets around negative numbers and a hyphen for 0 values

The precision of the displayed value is controilled by the '0.00'. You can use anything from '0' to '0.000000000000000'.

If you use a final ';' in the formatting switch with nothing following, (eg \# $,0.00;($,0.00) zero values will be suppressed. Note that this suppresses 0s resulting from empty fields and from fields containing 0s.


*Mailmerge Percentage Formatting*
In Word, add a formula and numeric picture switch to the mergefield. To do this:
. select your mergefield, which will look something like «Percent»;
. press Ctrl-F9 to wrap another field around it, thus - { «Percent» };
. edit the field so that you get {=«Percent»*100 \# 0.00%};
. position the cursor anywhere in this field and press F9 to update it.

Note: the precision of the displayed value is controilled by the '0.00'. You can use anything from '0' to '0.000000000000000'.

*Mailmerge Date Formatting*

To get the date format you want, you can add a formatting picture switch. In Word:
. select the mergefield;
. press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's name;
. delete anything appearing after the mergefield's name and add '\@ "d MMMM yyyy"' to the field, as in {MERGEFIELD MyDate \@ "d MMMM yyyy"}. With this switch your date will come out as '2 August 2008'. Other possible date formatting switches include:
  . \@ "dddd, d MMMM yyyy";
  . \@ "ddd, d MMMM yyyy";
  . \@ "d MMM yyyy";
  . \@ "dd/MMM/yyyy";
  . \@ "d-MM-yy";
    Note: you must use uppercase 'M's for months.
. position the cursor anywhere in this field and press F9 to update it.

As you can see, the above gives you the flexibility to define outputs formats for your mailmerge that differ from the source data fomats - if that's what you'd like to do. When you're done, run your mailmerge.

Cheers


----------



## DebbyG (Feb 4, 2009)

Thank you so much, that's such a fantastic reply. 

So much information and very easy to follow. 

Very much appreciated. 

)


----------



## Jakezils1 (Feb 16, 2009)

This post is awesome!  A Google search lead me to this site.  

I wanted to jump in and say THANK YOU! I've been fighting with Word for 3 hours and your information was VERY helpful!!! 

-Jake 
Florida, USA


----------



## Vogelmann (Mar 29, 2013)

Macropod said:


> Note: The '\# $0.00' in the field is referred to as a numeric picture switch. Other possibilities include:
> . \# 0 for rounded whole numbers
> . \# ,0 for rounded whole numbers with a thousands separator
> *. \# ,0.00 for numbers accurate to two decimal places, with a thousands separator*
> ...




I used the one bolded, but there is one issue here - if you number are from 0 to 1.000.000 (i.e. sometimes are bigger than 1.000, but sometimes smaller), the problem with the numbers that are smaller than 1.000 is that the thousand separator still exists - it means that, instead of let's say 454,39 you see .454,39. How to overcome this problem? Thanks in advance for the reply and sorry for refreshing, but I didn't want to create a new topic for such a small issue.


----------



## Macropod (Mar 29, 2013)

The issue for you is that your regional settings require the '.' and ',' to be reversed. Do that and the problem should be solved.


----------



## Macropod (Mar 29, 2013)

Cross-posted at: Mail merge format issue
For cross-posting etiquette, please read: Excelguru Help Site - A message to forum cross posters


----------

