(Tech Tip: Word) How to format numbers in a Word mail merge (sourcing Excel)

(Tech Tip: Word) How to format numbers in a Word mail merge (sourcing Excel)

Don’t struggle getting number formatting like $ signs, to show up correctly in a Word mail merge. This happens because Word looks at the underlying numeric value and ignores the cell formatting.

For what I consider the commonest of mail merge methods (Word sourcing Excel) , this is way harder than it should be.

Which option should you use?

If Option 3 works then use it. Option 3 is not intuitive to set up but it’s the easiest once it is set up because there are no formulas involved – your cell formatting is used.

Option 1 is also fine and is actually easier to set up since it’s just following the natural Word mail merge. But you have to get those “=TEXT” formulas in Excel.

Only use Option 2 if you have no ability to change the Excel file.

Option 1: Format the numbers in Excel, but as TEXT

Probably the Right-click the merge field and choose "Toggle field

Create some columns to the right of your numeric columns and use a formula like =TEXT(B2,"#,###;(#,###);-")

If you use these columns in your mail merge they will come out exactly as they appear in Excel because they are text values instead of numbers.

Option 2: Format the numbers in Word

Right-click the merge field and choose "Toggle field codes" (alt-F9) to show the underlying source of the number

Then you will see the name of the field (which you can adjust if you want)

Shortcut keys you should know:

Alt-F9 Toggle field codes

Ctrl-F9 Insert curly field codes (you can’t just type {} marks)

Ctrl-Shift-Space ‘Hard’ space – treats a phrase like a word.

Then you should type (or paste) in the formatting text just in front of the '}' mark.

the format being used is {MERGEFIELD "Performance" \# positive;negative;zero}

where you would put a format string for positive numbers in the positive slot, negative numbers in the negative slot, and zeros in the zero slot.

e.g.

{MERGEFIELD "Performance" \# $ #,##0.00;$ (#,##0.00);'-'}

would result in numbers like

$ 3,344.34

$ (65.00)

- (for 0)

Example1: A typical currency format (without decimals and with parens around negatives) would look like this:

to yield

Example2: Or with decimals and $ signs:

to yield

Example3:

This is the only way I could get zero values to print with ‘- ‘ formatting (hard spaces after the hyphen for right-justification)

When you are done, right-click and choose update field

Option 3: Use DDE between Word and Excel to preserve cell formatting

1. Make sure ‘Confirm file format conversion on open’ is checked in Word Options… General

2. Start your merge and select your Excel file as the data source. When you get this dialog , choose Show All and choose MS Excel Workseets via DDE

3. If things go well, you will now be able to paste fields with the cell formatting intact.

4. However on some machines, I got this unsolvable error and had to resort to Option 1.