5 Useful Ways to Keep the Number Format While Using Mail Merge in Your Word

In this article, we will present you 5 different ways to address the changing of number format while apply mail merge feature in Word.

Now and then, we will use mail merge function in Word to import a batch of data from Excel files. However, things can go in a very different way sometimes. For example, when it comes to data with decimals, we are likely to get a number with multiple decimal digits, which can undermine the document outlook. Below is an example:Original Number Format

Number Format Changes

Therefore, we here pick up 5 effectual methods for you to address such a problem. As this article will mainly focus on deal with the decimal issue, we will skip the steps of how to use mail merge feature in Word. Just for reference, you can read one of our previous articles: How to Create Personalized Letters or Emails in Batches via Mail Merge Feature in Word

Method 1: Alter Field Codes

  1. Firstly, after you insert merge field, you should not rush to click “Finish& Merge”. Instead, right click on the field.
  2. Then choose “Toggle Field Codes” on the list-menu.Choose "Toggle Field Codes"
  3. Now input “\#”0.00” just before the symbol “}”. To type “\#”0.00” means you want to keep 2 decimal digits in the number you get. Similarly, if you need just 1 decimal digit, you can enter “\#”0.0”.Alter Field Codes
  4. Next, place cursor inside field codes and press “F9” or right click and choose “Toggle Field Codes” again.
  5. Then you can click “Preview Results” to see if the results are right. After this, you can comfortably finish the left steps to fulfill the task.Click "Preview Results"

Method 2: Convert Source Data to Text Format

  1. To begin with, open the Excel File which contains source data.
  2. Then, select the target row or column of data.
  3. Next go to “Number” group under “Home” tab, and choose “Text” for “Number Format”.Select Column ->Choose "Text"
  4. Remember to save the Excel file.
  5. Then open Word file, and conduct the steps of mail merge.

Method 3: Insert a New Row into Table

  1. First thing, open the Excel file.
  2. Then select the row below the title.
  3. Next right click.
  4. And choose “Insert” on the menu to insert a new row into the table.Select Row 2 ->Right Click ->Choose "Insert"
  5. Then enter any letter you like in the cell above that of the first number. For instance, you may type “a” there.
  6. Next, when you open Word to start mail merge, you will finally get a page with letter “a”. Just delete it and the rest numbers shall remain in correct format.

Method 4: Convert Excel Table to a Word One

  1. First and foremost, open the Excel file.
  2. Secondly, click “File” tab.
  3. Then click “Save As” option.Click "File" ->Click "Save As"
  4. In “Save As” window, first find a storage location.
  5. Then choose “Web Page” in “Save as type”.
  6. Next, on the left-down corner, select “Selection: Sheet”.
  7. And click “Save” button.Find a Save Location ->Choose "Web Page" as Save Type ->Choose "Selection Sheet" ->Click "Save"
  8. Now in “Publish as Web Page” dialog box, first you choose the item to publish from the drop-down list of “Choose”.
  9. Next, click “Publish”.Choose Items to Publish ->Click "Publish"
  10. Now go to find the web file and right click on it.
  11. Next, choose “Open with”.
  12. And click “Microsoft Word” option to open the file in Word.Right Click on the File ->Choose "Open with" ->Choose "Microsoft Word"
  13. Now ensure there is no single space before the first paragraph mark. If any, just delete them.Delete Any Spaces Before the First Paragraph Mark
  14. And click “File” tab.
  15. Then click “Save As”.In Word Click "File" ->Click "Save As"
  16. In “Save As” window, choose to save the file either with extension of “.doc” or “.docx”.
  17. Then click “Save”.Choose Save Type ->Click "Save"
  18. Lastly, follow the steps to finish mail merge.

Method 5: Confirm File Format Conversion on Open in Word

  1. First, make sure the source file is saved in extension of “.xls” and the table includes source data is on the first sheet.
  2. Then open Word, and click “File” tab.
  3. Next click “Options” to open “Word Options” dialog box.Click "File" -> Click "Options"
  4. And choose “Advanced” next.
  5. Then scroll down to “General” page. Check the “Confirm file format conversion on open” box.
  6. Click “OK” to save change.Click "Advanced" ->Check "Confirm file format conversion on open" Box ->Click "OK"
  7. Next thing, after finishing editing the document, you click “Mailings” tab.
  8. Then click “Select Recipients”.
  9. And choose “Use Existing List”.Click "Mailing" ->Click "Select Recipients" -> Choose "Use Existing List"
  10. Now in “Select Data Source” dialog box, select the right file then click “Open”.Select Source File ->Click "Open"
  11. In “Confirm Data Source” dialog box, choose “MS Excel Worksheets via DDE”.
  12. Then click “OK”.Choose "Ms Excel Worksheets via DDE" ->Click "OK"
  13. Next the “Microsoft Excel” dialog box will pop up. Choose “Entire Spreadsheet”.
  14. And click “OK”.Choose "Entire Spreadsheet" ->Click "OK"
  15. Take the usual way to complete mail merge.

Comparison of 5 Methods

Methods Advantages Disadvantages
Method 1: Alter Field Codes 1.         Simple steps comparatively

2.         You can choose decimal digits.

3.         No need to change source file

4.         This method won’t add extra document page.

All data will have the same decimal digits.
Method 2: Convert Source Data to Text Format 1.       Simple steps comparatively

2.       Decimal digits will be the same as that in source file.

3.       This method won’t add extra document page.

It will alter the number format in source file.
Method 3: Insert a New Row into Table 1.       Simple steps comparatively

2.       Decimal digits will be the same as that in source file.

 

1.       You have to insert a new row insource file.

2.       You have to delete the extra page manually.

Method 4: Convert Excel Table to a Word One 1.       This method won’t add extra document page.

2.       Decimal digits will be the same as that in source file.

3.       No need to change source file

4.       You won’t be confined due to Excel file formatting.

1.       More steps comparatively

2.       You can only get data form one sheet each time.

Method 5: Confirm File Format Conversion on Open in Word 1.       This method won’t add extra document page.

2.       Decimal digits will be the same as that in source file.

3.       No need to change source file

 

1.       More steps comparatively

2.       It may require longer response time sometimes.

3.       The source file must be saved in “.xls” format.

4.       You can only get the data from the first worksheet.

File Corruption Always Find its Opportunity

In the above article, we talked operations between Word documents and Excel files. And as the demand varies, different methods are employed. But doubtlessly, all these moves can largely increase the possibility of getting Word files damaged. Then, a powerful Word file error repair tool is what you will badly need.

Author Introduction:

Vera Chen is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including damaged Excel xls repair and pdf repair software products. For more information visit www.datanumen.com

Leave a Reply

Your email address will not be published. Required fields are marked *