How-to: Make dates and currency in Excel show properly in a Word Mail Merge (Part 2)

 Sep 21, 2016

In my previous blog I talked about one way to make your dates and currency show properly in a Microsoft Word mail merge. This time I’ll be showing you 2 other methods!


Method #2: Change how the data looks in Excel itself using a formula

This one can be done entirely from within Excel.

When Excel shows you a date or currency in the grid, this is a format. Unfortunately, as we already know, this format doesn’t come across into a Microsoft Word mail merge.

There is a formula called =TEXT

It works by taking a real date or currency and converting it to the text version in the format that you specify.

How did I know to use “dd-mmm-yy” as the format? Simple, I looked in the Format Cells menu of Excel and copied it from there.

You don’t have to be an expert at these codes, just choose one that is close to what you want and then go down to the Custom option and see what it says.

Notice that this text version of sits on the left hand side of the cell (in column D) indicating that Excel now considers it a piece of text.

Now if I use both these columns in a word mail merge you can see that the Excel Date doesn’t work but that the Text Version does.

Notice the first one doesn’t show the format I want, the second does though! Cool!


Method #3: Change how the Microsoft Word mail merge field displays in the document

This option can be done entirely from Microsoft Word.

Start your mail merge and insert the field even though it won’t show the correct format.

Right click on the field and choose Toggle Field Codes

Note: If there is a piece of code on the end of the field code that says \*MERGEFORMAT then delete that, but don’t worry if isn’t there.

Add one of these codes on the end of the field code:

\# $#.##0.00

\@ dd-MMM-yyyy

Note that the \# or \@ defines whether it is a number or a date and the rest of the code shows how to format it. Do the codes look familiar? Yep, they are also from the Format Cells menu in Excel.

Note: The MMMs in month have to capitalised, otherwise it will treat lower case mmms as minutes.

Right click on the field again and choose Update Field.

And it looks great in the Word mail merge document!

Come along and learn more about mail merges at New Horizons in our Word level 2 course.


How do your Word skills stack up?   

Test Now  

How do your Excel skills stack up?   

Test Now  

About the Author:

Matthew Goodall  

Matthew is a qualified Microsoft Office Specialist, Microsoft Certified Applications Specialist and a Microsoft Certified Trainer with over 11 years of hands-on experience in a training facilitation role. He is one of New Horizons most dynamic instructors who consistently receives high feedback scores from students. Matt enjoys helping students achieve real professional and personal growth through the courses he delivers. He is best known for creating “fans” of students, who regularly request him as an instructor for any future courses they undertake at New Horizons.

Read full bio
top
Back to top