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.

Wrong Values Merged from Excel

Written by Allen Wyatt (last updated June 14, 2022)
This tip applies to Word 2002 and 2003


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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Removing All Text Boxes In a Document

Text boxes are a common element of many types of documents. At some point you may want to get rid of all the text boxes ...

Discover More

Files Opening Slowly If Many Files Exist

Managing large numbers of documents in Word can lead to some interesting challenges. One potential challenge is that your ...

Discover More

Clearing All Tabs in a Document

Need to get rid of all the tab stops in a particular document? It's easy to do when you apply the technique outlined in ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More WordTips (menu)

Merging with Two Data Sources

Setting up a Word mail merge to combine a data source with a merge document is easy. But what if you want to use two data ...

Discover More

Getting Rid of Blank Labels in a Filtered Merge

If you are filtering a mail merge in Excel, and you get blank labels in the printout in Word, chances are good that ...

Discover More

Can't Merge Alphanumeric Data Correctly

When you merge data from Excel into a Word document, you may need to do some conditional processing based on the data you ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 8?

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


This Site

Got a version of Word that uses the menu interface (Word 97, Word 2000, Word 2002, or Word 2003)? This site is for you! If you use a later version of Word, visit our WordTips site focusing on the ribbon interface.

Videos
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.