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:
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
- Firstly, after you insert merge field, you should not rush to click “Finish& Merge”. Instead, right click on the field.
- Then choose “Toggle Field Codes” on the list-menu.
- 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”.
- Next, place cursor inside field codes and press “F9” or right click and choose “Toggle Field Codes” again.
- 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.
Method 2: Convert Source Data to Text Format
- To begin with, open the Excel File which contains source data.
- Then, select the target row or column of data.
- Next go to “Number” group under “Home” tab, and choose “Text” for “Number Format”.
- Remember to save the Excel file.
- Then open Word file, and conduct the steps of mail merge.
Method 3: Insert a New Row into Table
- First thing, open the Excel file.
- Then select the row below the title.
- Next right click.
- And choose “Insert” on the menu to insert a new row into the table.
- Then enter any letter you like in the cell above that of the first number. For instance, you may type “a” there.
- 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
- First and foremost, open the Excel file.
- Secondly, click “File” tab.
- Then click “Save As” option.
- In “Save As” window, first find a storage location.
- Then choose “Web Page” in “Save as type”.
- Next, on the left-down corner, select “Selection: Sheet”.
- And click “Save” button.
- Now in “Publish as Web Page” dialog box, first you choose the item to publish from the drop-down list of “Choose”.
- Next, click “Publish”.
- Now go to find the web file and right click on it.
- Next, choose “Open with”.
- And click “Microsoft Word” option to open the file in Word.
- Now ensure there is no single space before the first paragraph mark. If any, just delete them.
- And click “File” tab.
- Then click “Save As”.
- In “Save As” window, choose to save the file either with extension of “.doc” or “.docx”.
- Then click “Save”.
- Lastly, follow the steps to finish mail merge.
Method 5: Confirm File Format Conversion on Open in Word
- First, make sure the source file is saved in extension of “.xls” and the table includes source data is on the first sheet.
- Then open Word, and click “File” tab.
- Next click “Options” to open “Word Options” dialog box.
- And choose “Advanced” next.
- Then scroll down to “General” page. Check the “Confirm file format conversion on open” box.
- Click “OK” to save change.
- Next thing, after finishing editing the document, you click “Mailings” tab.
- Then click “Select Recipients”.
- And choose “Use Existing List”.
- Now in “Select Data Source” dialog box, select the right file then click “Open”.
- In “Confirm Data Source” dialog box, choose “MS Excel Worksheets via DDE”.
- Then click “OK”.
- Next the “Microsoft Excel” dialog box will pop up. Choose “Entire Spreadsheet”.
- And click “OK”.
- 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