Mail merge data from excel not matching format in excel

I am mail merging data from excel and i have data that is formatted as number 9,000 and percentages 35% in excel and the data in word is showing up as numbers 900000.00 and 35.0006788. How can i correct the merge format to match what is on my source file?

Welcome to the world of mailmerge. Word has a number of different methods of connecting to mail merge data sources, including DDE and OLE DB. Word 2002 and later use the OLE DB connection by default, which pulls in the data but not their formats, though you can change this (to DDE, for example). The following should get you through all the common scenarios:

Mailmerge Number & Currency Formatting
To control number & currency formatting in Word, add a numeric picture switch to the mergefield. To do this:
• select the field;
• press Shift-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.
Note 1: 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 controlled 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.
Note 2: If you use a decimal tab or right-aligned tab to align the values, wrap the switch in quotes (i.e. \# "$,0.00") and insert a tab into the field code after the $ sign, you can have the values output like:
$ 999,999.99
$ 9,999.99

Mailmerge Percentage Formatting
To control percentage formatting in Word, add a formula and numeric picture switch to the mergefield, as follows:
• 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.

Mailmerge Phone Number Formatting
To control Phone Number formatting in Word, all you need to do is to add a picture switch to the mergefield, as follows:
• select the mergefield;
• press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyData}, where 'MyData' is your data field's name;
• delete everything between 'MyData' and the closing field brace;
• add ' \# "(000) 0000 0000"' after 'MyData', so that you end up with {MERGEFIELD MyData \# "(000) 0000 0000"};
• position the cursor anywhere in this field and press F9 to update it.

Mailmerge Date Formatting
When Word is connected to an OLE DB data source, it treats dates as if they are in the US mm/dd/yyyy format, regardless of the format in Excel, your regional settings etc. To get the date format you want, you can add a formatting picture switch as follows:
• 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 dates will come out like '2 August 2008'.
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.
Other possible date formatting switches include:
. \@ "dddd, d MMMM yyyy";
. \@ "ddd, d MMMM yyyy";
. \@ "d MMM yyyy";
. \@ "dd/MMM/yyyy";
. \@ "d-MM-yy".
Note 3: You can swap the d, M, y expressions around, but you must use uppercase 'M's for months - lowercase 'm's are for minutes.

One thing you'll also notice if you're using ordinary date fields in the mailmerge main document is that the date in the output document will update if you re-open the document at a later date, which can be problematic. There is a simple way to prevent this - embed the Date field in the mailmerge main document in a QUOTE field. To do this, simply select the date field, press Ctrl-F9 to embed it in another field, and type 'QUOTE' inside the left field brace, so that you end up with {QUOTE 'date field'}. With the QUOTE field, the date field will automatically unlink when the mailmerge output document is created.

Mailmerge Time Formatting
To get the time format you want, you can add a formatting picture switch as follows:
• select the mergefield;
• press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyTime} where 'MyTime' is your mergefield's name;
• delete anything appearing after the mergefield's name and add '\@ "h:m"', as in {MERGEFIELD MyTime \@ "h:m am/pm"};
• if you want the hours and/or minutes to display leading 0s, change 'h' to 'hh for hours and 'm' to 'mm' for minutes;
• position the cursor anywhere in this field and press F9 to update it.

Mailmerge US Social Security Number Formatting
The following field suppresses all except the last four digits in a mergefield that gets its data from a field named ‘SSN’, where the data are formatted as ‘123-45-6789’.
{QUOTE
{SET ID {MERGEFIELD SSN}}
{SET Part3{=({ID}*(-1)-ID)/2}}"XXX-XX-"{Part3 \# 0000}}

Mailmerge US Zip Code Formatting
The following field code correctly formats 5-digit and 5+4-digit Zip Code data, even where the source data may have incorrect formatting such as a hyphen followed by trailing 0s after plain 5-digit Zip Codes (eg because a db only accepts Zip Codes in the 5+4-digit format) or is formatted as a 5-digit or 9-digit string without hyphens. A data field named ‘ZipCode’ is assumed.
{QUOTE
{SET Zip {MERGEFIELD ZipCode}}
{IF{Zip}> 99999 {SET Zip {Zip \# "00000'-'0000"}}}
"{=-{=-{Zip}-Zip}/2 \# 00000;;}{=-({Zip}*(-1)-Zip)/2 \# ;-0000;}"}

Force '0' Substitution for Empty/Missing Records
You can force the substitution of $0.00 for missing records via the following process:
1. Select your mergefield. For the purposes of this example, I'll suppose it's named 'Value' and shows up as '«Value»' in your mailmerge main document;
2. Press Ctrl-F9 to embed your mergefield in another field, so that you get '{«Value»}';
3. Type 'Set Val' into the '{«Value»}' field, so that you end up with '{Set Val «Value»}';
4. After the newly-inserted field, press Ctrl-F9 again to insert another (empty) field, so that you get '{Set Val «Value»}{ }';
5. Type '=Val \# "$,0.00"' into the second field, so that you end up with '{Set Val «Value»}{=Val \# "$,0.00"}'.

Basic Mailmerge Maths
You can create the formula in Word to perform maths on a mergefield. For example, suppose your data include the final price of an item for which you need to show how much is the base price and how much is the tax component, where the tax is 10% of the base price (ie 1/11th of the final price):
• select your mergefield, which will look something like «Price»;
• press Ctrl-F9 to wrap another field around it, thus { «Price» };
• to calculate the tax component, edit the field so that you get {=«Price»/11 \# "$,0.00"};
• to calculate the base price, edit the field so that you get {=«Price»*10/11 \# "$,0.00"};
• position the cursor anywhere in this field and press F9 to update it.


Note 4: The field brace pairs (ie '{ }') for all of the above field code examples are created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues. Likewise, you can't type or copy & paste the chevrons (ie '« »') - they're part of the actual mergefields, which you can insert from the mailmerge toolbar.

Thanks for your help

b624333 1st, "Nothing is working when I try formatting the mail merge in word." is hardly descriptive of your results

2nd, Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.