Loading
Word.Tips.Net WordTips (Menu Interface)

Wrong Values Merged from Excel

Please Note: This article is written for users of the following Microsoft Word versions: 2002 and 2003. If you are using a later version (Word 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Word, click here: Wrong Values Merged from Excel.

Derek is performing a mail merge in Excel, but some of the values are being imported incorrectly. For instance, a value that appears as 5.2 in Excel may be imported into Word, during the merge, as 5.20000000002. Derek is wondering why this occurs.

It is important to understand that the value in Excel is more than likely 5.20000000002, but is rounded or formatted in the worksheet so that only 5.2 is displayed.

Assuming you are using Office 2002 or Office 2003, the actual merging of data from the Excel workbook is done using a technology called OLEDB. This technology, unlike the older DDE or ODBC technologies, ignores the formatting of anything that is merged. Thus, you get the full number instead of the rounded or formatted number.

There are two possible solutions to this problem. The first has to do with changing the data in your Excel worksheet. This approach will only work if the values being merged from Excel are, in fact, values and not formulaic results. In that case, simply make sure that the shorter value (5.2) is in the cell instead of the longer value (5.20000000002).

The second solution is to modify the merge field used to transfer the value to the Word document. This solution works great if the values being merged in a specific field all follow the same general pattern. For instance, let's assume that the merge field normally appears as follows:

{MergeField MyValue}

If you want the field to always display the merged value with a single digit to the right of the decimal point, you can change the field so it appears like this:

{MergeField MyValue \# ""0.x""}

The curly brackets represent the merge field braces; they are not typed. You can display your current merge field by selecting it and press Alt+F9. Then you can add the appropriate switch. You can find additional information on how to format using picture switches at this Web site:

http://www.gmayor.com/formatting_word_fields.htm

WordTips is your source for cost-effective Microsoft Word training. (Microsoft Word is the most popular word processing software in the world.) This tip (330) applies to Microsoft Word 2002 and 2003. You can find a version of this tip for the ribbon interface of Word (Word 2007 and later) here: Wrong Values Merged from Excel.

Related Tips:

The First and Last Word on Word! Bestselling For Dummies author Dan Gookin puts his usual fun and friendly candor back to work to show you how to navigate Word 2013. Spend more time working and less time trying to figure it all out! Check out Word 2013 For Dummies today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.