How To Get Mail Merge To Keep Excel Formatting

One of the perennial annoyances of Postal service Merge is its inability to format numbers from an Excel spreadsheet correctly. Fortunately, there is a way to fix this. In fact, there are three ways.

[Originally posted June 2022. Terminal updated in June 2022. For a video on this topic, click: https://youtu.exist/otPTkvnrPlc.]

The Mail Merge feature of Microsoft Word is one of my favorite parts of the program. Information technology is extremely powerful for creating labels and customized letters, emails, or reports. Once you lot learn how to utilize it, you lot can save countless hours of work. Unfortunately, the task of learning to master all of its idiosyncrasies can give you countless headaches.

I of the perennial annoyances of Post Merge is its inability to format numbers from an Excel spreadsheet correctly. For instance, a sales outcome in Excel appears as 100 simply in Discussion it suddenly becomes 99.99999999996!

Fortunately, there are no less than 3 different means to go Mail Merge numbers to appear the style they should.

[Annotation for users of diverse versions of Microsoft Office: The steps presented here are for Office 2022 as of August 2022, but the steps needed for older and newer versions of Give-and-take and Excel are very similar if not identical.]

one. Change the Excel Spreadsheet

The kickoff style is to avert the problem: In the Excel worksheet, insert a column with a formula that converts the Excel numbers or dates into a text format that is exactly the same as what you would want to announced in your Postal service Merge document.

To practice this, you demand to use Excel'southward TEXT role, which enables you to convert a number or appointment into its equivalent text formatted exactly the way yous desire. The downside is that you need to know sure formatting codes. Although these codes are identical to those used in the Custom Number formatting feature of Excel, they are rarely seen past almost Excel users.

For example, the formulas TEXT(B3, "$#,##0.00") and TEXT(C5, "G/dd/yy") convert number and date data into textual data (in this case, "$12,345.67" and "12/01/10").

(Tip: It is a good idea to format the cells containing these formulas in a unlike style, say, italic, or some unusual color to remind you that these numbers are just text. In older versions of Excel, these "numbers" could not be used in calculations. In Excel 2022, they can be used in all formulas except aggregate functions, such as, SUM and AVERAGE.)

The advantage of formatting numbers and dates every bit text is that text is transported from Excel into a Word Mail Merge document unmolested. Well, nigh unmolested. Formatting options such as font, size, and color do not make the trip.

Format Codes for Excel's TEXT Part

A collection of the nearly commonly used codes is presented at the right. A fundamental thing to think is that both "0" and "#" act as placeholders for digits, simply "0" will force leading or trailing zeros to appear. The "#" placeholder will be replaced past a digit merely if it is non a leading or trailing cypher. Thus, the code "00000" will ensure that the leading cipher is not truncated from New Jersey postal zip codes.

These codes for numbers, dates, and times can exist used in the TEXT function or in Excel's Custom number formats. As an extra bonus, they can also be used in the "Numeric Switches" in Discussion Post-Merge Fields described in the next department.

Unfortunately, there is a slight departure between the mode these codes piece of work in Excel and Word in Microsoft Office 2022. In Excel, you lot tin can use either "One thousand" or "m" for months or minutes. In virtually all cases, Excel is smart enough to figure out which units you are talking about. But Word is not so smart, and you must use the capitalized letter of the alphabet to refer to months. Also, Excel has an additional code, "MMMMM", which returns a single letter abbreviation for the month (e.thousand., "J, F, M, A, …").

Supplementing a spreadsheet table with a few columns featuring TEXT functions is a elementary and direct approach. Just remember that these cells may look like they incorporate numbers, but they cannot exist used in SUM or AVERAGE functions. If you demand to convert them into numbers again, just apply the VALUE function.

Handle with Care: The Circular Function

Instead of the TEXT() function, some users prefer to use the ROUND() function to trim off actress decimal places. Every bit its proper name implies, Excel's Round() part will permanently circular a number up or down to the number of decimal places you specify. The advantage is that the rounded number is not text and can still be used for farther calculations. In many cases, the Circular() function volition work well with Mail Merge, only y'all may want to steer clear of it considering of the following reasons:

1. The ROUND function will not preserve the dollar sign or the thousands separator (comma)
2. The ROUND function will truncate trailing zeros
3. The ROUND function sometimes causes Postal service Merge to display the wrong number of decimal places. For example, a Mail Merge document occasionally shows 4 decimal places when the ROUND function had specified 2 or 3.

In rare cases, the Round office causes Postal service Merge to show a slightly different number. For instance, instead of displaying ane.0014, Mail Merge showed 1.0013.

Okay. Merely allow'south say that you can't or don't desire to change your Excel spreadsheet. Well, there is something we can practice in Discussion:

2. Use a "Numeric Switch" in Word

The second way to cajole Mail service Merge into displaying numbers from Excel correctly is to modify the Word document. Specifically, this means applying the desired number format code to the Merge Fields in the Word document. (The merge fields basically tell the Word document which column in the Excel table has the desired data.) To employ a format to a field, you lot must include a numeric switch (formerly chosen a film switch) in the field's field code.

The showtime thing you lot have to exercise is to see the actual field code. Open up the Mail Merge certificate and click the Mailings tab at the top of the window. Be certain the Preview Results button is toggled off so that y'all tin run into the Mail-Merge fields. So right-click a Postal service-Merge field (such as «Donation») and choose the Toggle Field Code pick. Yous should at present see the actual field code for that field, which is designated by curly braces as in { MERGEFIELD Donation }. Now edit the field lawmaking past simply inserting a numeric switch code to the cease of the field code, every bit in

          { MERGEFIELD Donation \# $#,##0.00 }        

There are many motion-picture show codes available. Here are four examples with their respective results:

          { MERGEFIELD Cost \# 00.00 }              01.00 { MERGEFIELD Sales \# $### }               $ 15 { MERGEFIELD Sales \# $#,##0.00 }    $ i,500.00 { MERGEFIELD Date \@ "MMMM d" }     November 26                  

As yous can see, the numeric switch codes are identical to the Excel formatting codes except that they are preceded by "\#" for numbers and "\@" for dates. (Annotation the use of quotation marks in the date lawmaking just not in the number codes.) To run across more than of the codes bachelor, refer to the table beneath, or see the online help for "numeric switch" in Microsoft Discussion.

Here are some examples of how the numeric field codes work with data in a cavalcade labeled "Sales" in an Excel spreadsheet.

Numeric Switches for Mail-Merge MergeFields

In light of the fact that Word and Excel use the same formatting codes, nosotros could not help wondering why they don't use the same function format. For example, why not accept the MergeField role look something like:

          { MERGEFIELD(Donation,"$#,##0.00") }        

Skillful question. You'll accept to ask Microsoft.

Note: Earlier you outset cursing me out, try to remember that when you add or modify a numeric switch, the effect may non exist shown immediately. You either take to update the field (right-click information technology and choose Update Field), or click the button Mailings > Preview Results. On one occasion I had to do this two or 3 times. If you lot are using the Mail-Merge Wizard, you may have to go back a stride and return to encounter the consequence of your changes. (Why? Again, yous will accept to inquire Microsoft.)

The numeric switches in merge fields work well, but I find the process very difficult to remember. ("Is information technology a forward slash or a back slash?") Also, it is very like shooting fish in a barrel to make a mistake. Fortunately, for some users there is some other way:

3. Utilize a DDE Link

The in a higher place approaches are relatively simple, just if you have more than than a few fields that crave formatting, they tin can drive you into early on retirement. At the very least, they crave you to remember format codes that, while similar, are used in very different ways.

A much more than elegant and unproblematic solution is to have Word link to the Excel workbook via a DDE (Dynamic Data Exchange) link rather than the usual, presumably non-dynamic, linking process. That sounds a lilliputian daunting, just if you are smart enough to do Postal service Merge, then DDE can a piece of cake. It is a unproblematic two-footstep procedure, and the starting time step — enabling Word to open a file via DDE — has to be washed simply once.

[Alarm: Does DDE piece of work in Office 2022 or 2022? Dynamic Data Exchange is an old technology, and it looks like Microsoft is in the procedure of pulling the plug on it. Sometimes it works for me in these adequately new versions, but lately it rarely works.

One trick you could endeavor is to exist certain that the Excel data source is open before you access information technology from Word. As usual for Excel data sources, the table must begin on the first row of the first worksheet in the workbook file. If you lot do this, and have some patience, yous should exist able to go it to work. But, beware: It may not work all the time. ]

To prepare Discussion for DDE links, do the following:

• Click: File > Options.
• Click the Avant-garde tab on the left and coil downwards to the section Full general.
• Check the box labeled Ostend file format conversion on open.

That'due south all for the starting time step, and you lot never take to practice it again. From now on, your copy of Word tin open upwardly many different types of files, and tin open these by different avenues, including DDE. The but side outcome of the above is that every time yous open up a non-Discussion file with Discussion, the program will give you a chance to change your mind. No problem.

The second and last step has to be done each time you lot select a data source for your Mail Merge performance (either in Pace 3 of Word'due south Post Merge Wizard or after you press the Select Recipients push button in the Mailings ribbon). Relax. It is just three boosted mouse clicks:

• After you have chosen the data file you lot would similar to utilize, a new "Confirm Data Source" dialog box will appear.
• The default type of link is by OLE, but that is not what you want.
• In the Confirm Data Source dialog box, click the bank check box to Show all.
• In the expanded listing of file types, choose MS Excel Worksheets via DDE (*.xls). (Choose this fifty-fifty if you are using the newer Excel file format: *.xlsx.)
• If asked, confirm that you are selecting theEntire Spreadsheet.

If you have already selected a spreadsheet for your Word document, you may accept to select it once more, this time via a DDE link. That's information technology! From at present on, your Excel formatting will travel over to Word Mail Merge documents fairly intact. One huge caveat hither: Brand sure that the data you want to merge are in the first canvas of your Excel workbook. (It took us two hours to finally figure out that DDE does non come across anything but the first Excel worksheet!)

It is a shame that Microsoft has not replaced DDE with a new technology that works in a similar way. When DDE worked, it was marvelous. Let'south promise an equally marvelous engineering science will announced in the near time to come.

---

Does DDE piece of work for you in Discussion 2022 or 2022? If yous have a comment about that or any other issue related to Mail Merge number formatting, please let me know. Click hither to mail an anonymous annotate.

Source: https://techhelptoday.com/3-ways-to-fix-mail-merge-number-formatting-in-microsoft-word/

Posted by: hawkscitine.blogspot.com

Share this post