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:
\@ 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 Excel skills stack up?
Test NowNext up:
- Convert imagination into art with Adobe Capture
- Create cascaded dropdowns in Nintex forms
- Story-Telling leads to Story-Selling: From Content to Insights to Revenue (Infographic)
- How to get the most out of Outlook
- Tools and methods for identifying learning and development needs
- How-to: Put SharePoint calendars into Outlook
- Unleash creativity in the workplace
- Lightroom vs. Photoshop’s Camera Raw
- The importance of a break-even point in budgeting
- Semantic Tags and the File API in HTML5
Previously
- Convert imagination into art with Adobe Capture
- 10 habits of highly effective social sellers (Infographic)
- See the blue through the clouds with Microsoft Azure!
- Reduce those pesky meeting request response messages in Outlook
- Whats new in Windows Server 2016 Hyper-V
- The first tool of Power BI
- Use conditional formatting to create a Gantt Chart in Excel
- How-to: Add public holidays to a Microsoft Project 2013/2016 calendar
- What they don't tell you about becoming a manager
- Have you got bubbles?