Seems like ever since version 2002/XP, formatting just isn't right in mail
merges anymore. A few of the problems that are caused:
- Leading zeroes are dropped from zip codes
- Currency doesn't show up with a dollar sign and two decimals
- Dates come out wrong
After you alter the field codes, you must update the fields to see the
result. This is accomplished by any of the following methods:
- Print preview
- Save, close and reopen
- Ctrl+A to select all, then F9 to update
Here's the key. Our sample data source (in Excel) looks like this:
Create your mail merge normally and preview one of the records. If the field
doesn't come out right, then turn on viewing of your fields codes. This is most
easily done by hitting Alt+F9 (it's a toggle, so hit Alt+F9 to turn off again
later). Then, a zip code merge field will look like this.
Note that the zip code has lost its leading zeroes, and the
currency has lost it's dollar sign. The date seems okay, but maybe we want it to
appear as June 15, 2005 instead. So let's see how it's done.
Hit Alt+F9 or go to ToolsŕOptionsŕView
tab and turn on viewing of Field Codes. The same document now displays as
follows. I've widened the table cells so the fields are easier for us to read
Now edit those field codes to read as shown in the next graphic.
I've changed the font so it's more easy to read the text in the merge fields.
The result is quite a bit different.
Great information about merge field codes can be found at:
OfficeArtilces.com debuted on May 26, 2005.
MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on
this web site are provided "as is" and we do not guarantee that they can be used
in all situations.
Access®, Excel®, FrontPage®, Outlook®, PowerPoint®, Word® are registered
trademarks of the Microsoft Corporation.
MrExcel® TM is a registered trademark of Tickling Keys, Inc.
All contents © 1998-2014 by MrExcel Consulting | All rights reserved