Have you ever attempted to mail merge with fields that contain formatted numbers, only to find that the formatting does not carry over onto your merged Word document? This is a common source of frustration with mail mergers, and something that we can quickly remedy on the Excel end. Let’s take a look… but first, big thank you to Jamie for having this excellent question at Wednesday’s Excel Essentials session and inspiring this Byte.
Exercise Files
If you would like to follow along, download today’s exercise files below to your desktop.
For this Byte, I am assuming you know the basics of working with Mail Merge in Word. If you are not comfortable with Mail Merge, please attend a Word Essentials training with me! I would love to show you how it works.
Excel File
Let’s take a look at the Excel file first. This is a list of employees… here are all out new professors who we are going to send letters to, welcoming them to Starfleet.
There are columns with Last Name, First Name, Salary, FTE, and Phone Number. The number fields are all formatted: Salary as currency, FTE as rounded to two decimals, and Phone Number as the Special Phone Number format.
Heads up: Notice that, highlighted in yellow, there are also some “helper columns” with TEXT titles. Let’s ignore those for the present, because in real life, perhaps you haven’t created those, and instead incorporated number formatting, as most of us do in our Excel Files
Word File
Opening up the Word File, you will find a letter welcoming professors to Starfleet Academy. Our plan is to enter merge fields into the bold area circled below.
Mail Merge with Formatted Number Columns
Let’s start our merge. We are going to build this using our number formatted columns. Again, I am assuming you have merged before for this exercise. If you are not comfortable with Mail Merge, please come attend one of my Word Essentials session. I would love to show you!
1. In the Mailings tab, go to Start Mail Merge, and select Letters.
2. For Select Recipients, select Use an Existing List. Browse for the Excel file, wherever you chose to save it.
3. Take a look at the Insert Merge Field dropdown, and make sure that you see all the columns you saw in your Excel file. Remember, we are focusing on the first five merge fields for now (not the TEXT ones).
4. Insert each merge field in the indicated location. It should look like the example below.
5. Let’s toggle the Preview Results button to see how this will look.
This is usually the point where people realize that certain types of number formatting do not come through in Mail Merge. The salary, phone number, and FTE are just general number formatting. The phone number has no dashes, the salary has no commas, and the FTE is missing a “0.”
6. Let’s remove the Phone, Salary and FTE merge fields, and try this again. This time, insert the TEXT options that we saw in our “helper” columns in the Excel file.
So Salary should be replaced with TEXTSal, FTE with TEXTfte and Phone with TEXTPh.
7. When you Preview results, it will look much more like what we had in mind.
What difference! Let’s see what makes these columns different in our Excel file.
Text Formulas
Open the Excel file again, and let’s do some investigating. We are going to check out our “helper” columns in columns F, G, and H that contain TEXT in the header.
1. Let’s see how the formula for TEXTSal is set up. Click into cell F2, and take a look in the formula bar. The formula used for Sal was:
=TEXT(C2,”##,##0″)
2. Do the same for TEXTfte in G2. The formula used was:
=TEXT(D2,”#0.00″)
3. … and finally, TEXTPh in H2. The formula used was:
=TEXT(E2,”[<=9999999]###-####;(###)###-####”)
So our investigating reveals that TEXT formulas have been used in these columns, which came over in a much better format for mail merge purposes.
If you would like to learn more about the TEXT function, take a look at this handy guide from Microsoft that will walk you through creating all of these used above, and more!
Thoughts?
Has this ever happened to you when you were mail merging with numbers? Do you think you will incorporate TEXT functions with your future merges?
Congratulations, Power Users!
Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser
Haley Underhill
