The tutorial shows how to keep formatting of numbers, dates, percentage and currency when doing a mail merge from Excel to Word or change it to your liking. Show
In one of our previous articles, we looked at how to mail merge from Excel to Word to send personalized letters or email messages. As it turned out, using Word's Mail Merge to automate the creation of a document from an Excel worksheet may present lots of challenges. Some fields might not be populated or filled with wrong information. Numbers properly formatted in Excel may not appear correctly in a Word document. Zip codes may lose leading zeros. The troubleshooting tips below will help you fix typical Mail Merge formatting problems. How to mail merge and keep formattingWhen performing a mail merge from an Excel worksheet, some of your numeric data may lose formatting after coming through the merge. The problems typically occur with numbers formatted as percentage or currency, or numbers containing leading zeros such as zip codes. Cause: By default, Microsoft Word uses the OLE DB connection, which pulls in the information but not formats. As a result, in a Word document, the data appears in the format in which it is stored internally in Excel, and not in the format applied to cells. To illustrate the problem, please have a look at the source data formats in an Excel worksheet: Now, see what happens in a Word mail merge document:
Solution: For Word to display postal codes, dates, prices, percentages and other numeric values in the right format, connect to your Excel workbook using Dynamic Data Exchange (DDE). How to use Dynamic Data Exchange to connect to Excel sheetBefore starting a mail merge, perform the following steps in Microsoft Word.
It may take Word quite a while to connect to your Excel data source via DDE, so please be patient - this will save you even more time in the long run :) Tip. To prevent multiple prompts displayed by Word every time you open the data file, clear the Confirm file format conversion on open check box after connecting to your mailing list. Now, all numeric values in the Word mail merge document retain their original formats. How to format specific mail merge fieldsConnecting to an Excel worksheet via DDE is the fastest way to fix all formatting problems in one go. If this solution is not applicable to you for some reason, you can control the formatting of Excel data in Word by adding a numeric switch (formerly called a picture switch) to a particular merge field. A numeric switch is a kind of mask that lets you format the contents of a field in a Word document the way you want. To add a numeric switch, perform these steps:
The following examples will get you through a few typical scenarios. Mail merge: number formatTo get numbers to appear in the right format (the same or different than in your Excel file), use the following numeric switch codes.
For example, to format a whole number with a thousand separator, use the numeric switch \# ,0 so the Number field changes to:
Mail merge: currency formatTo format currency in mail merge, these are the codes to use:
For example, to apply a dollar format in mail merge, add the numeric switch \# $,0 to the Currency field:
As a result, the number 3000 gets formatted as $3,000 with no decimal places. Tip. The above examples are for the mail merge dollar format. Instead of the dollar sign ($), you can use any other currency symbols, e.g. € or £. Mail merge: percentage formatThe way you format percentages in a Word mail merge document depends on how the source values are formatted in your Excel sheet. If the General or Number format is used in Excel, a corresponding number will appear in Word. To format that number as a percent, add one of the following numeric switches.
For instance, to format a number as a whole percent, edit the Percent field as follows:
As a result, the number 50 is formatted as 50%. If the Percent format is applied to Excel cells, Word will display an actual value behind the percentage formatting, which is a decimal number. For example, an Excel value of 50% will appear as 0.5 in Word. To convert it to a percent, you need to first multiply a decimal by 100, and then use an appropriate numeric switch. Here are the detailed steps:
Mail merge: date and time formatAs with numbers and currency, you can change the date format in mail merge by using a numeric switch. The table below lists codes for a few commonly used date/time formats.
For example, to apply a full date format, change the Date merge field format in this way:
Tips and notes:
How to change format of the current date and time in Word mail mergeTo insert today's date and current time in a mail merge document, you can use the following shortcuts:
This will add the date and time in the default format. To change it, you can use a numeric switch as described above. Or you can apply the desired date/time formatting in a visual way.
How to avoid losing leading zeros in mail mergeIn all numeric values, leading zeros are dropped during a mail merge. For zip codes and other numbers to come through a mail merge without losing zeros, they should be formatted as text. To have it done, select the column with numbers and choose Text in the Number Format box on the Home tab. Alternatively, you can right-click the selected column, and then click Format Cells… . In the Format Cells dialog box that opens, on the Number tab, select Text, and then click OK. Important note! If Special (such as Zip code) or Custom (such as 00000) format is applied to your Excel cells, changing it to the Text format will cause leading zeros to disappear. After formatting cells as Text, you will have to review each cell and type missing zeros manually. To avoid this daunting task, connect to your Excel sheet via DDE. This will retain the original Excel number formats including preceding zeros. That's how to resolve mail merge formatting issues and format the fields the way you want. Thank you for reading! You may also be interested inWhat allows you to position the data correctly in a mail merge?4. To position the data correctly in a Mail Merge document, you need to use field placeholders.
What items are merged during a mail merge?The mail merge process involves taking information from one document, known as the data source, combining with another document known as the main document. The data source is a document, spreadsheet or database that contains personalized information such as names, addresses, and phone numbers.
Which of the following are the correct steps in creating a simple mail merge?Set up and Choose Document Type. Click the Mailings tab.. Click the Start Mail Merge button.. Select Step-by-Step Mail Merge Wizard. The Mail Merge pane appears on the right, ready to walk you through the mail merge.. Select a type of document to create.. Click Next: Starting document.. What are the 3 data source that can be used to perform mail merge?Here are few examples of data sources you can use for mail merge in Word.. Excel spreadsheet. An Excel spreadsheet works well as a data source for mail merge. ... . Outlook Contact List. You can retrieve contact information directly from your Outlook Contact List on to Word. ... . Apple Contacts List. ... . Text files.. |