Please Note: This article is written for users of the following Microsoft Word versions: 97, 2000, 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: Can't Merge Alphanumeric Data Correctly.

Can't Merge Alphanumeric Data Correctly

by Allen Wyatt
(last updated January 14, 2017)

Dave is having a problem getting data in an Excel worksheet to merge successfully into a Word document. The original Excel file had fields like Job Number and Truck Number. In the merge file he wanted the value of these fields to display unless the field was blank, in which case he wanted some underscore characters displayed. Dave used the following as his merge field:

{IF{MERGEFIELD Job_Number}="" "________" "{MERGEFIELD Job_Number}"}

When both the Job Number or Truck Number fields used the General format, and contained only digits, this worked fine; Dave got the numbers (or underlines when the field was blank). Then his data changed and he had job and truck numbers with alphanumeric characters. When doing the merge, the number 0 displayed in the merge in any place where the job or truck number contained alphanumerics. Thus, the merges now produced 0 in place of alphanumeric fields and Dave never got underlines; only numeric fields (or numeric fields formatted as Text) merge properly.

There are a couple of things Dave can try in this situation. The first is to modify, slightly, the merge field. If the merge is grabbing zero values instead of blanks, then you can change the test so it checks for zeroes, as shown here:

{IF{MERGEFIELD Job_Number}="0" "________" "{MERGEFIELD Job_Number}"}

Now, if Excel is really transferring zeroes in place of blank fields (or Word is interpreting blanks as zeroes), the test will be positive and you will get the underlines you want.

Another thing to check out is the formatting on the data that is being imported. Beginning with Word 2002 (well, with Office XP really), Microsoft changed which "end" of the merge pair was responsible for formatting. When merging from Excel, it used to be that Excel was responsible for formatting. Beginning with Office XP, the responsibility shifted to Word. This means that the complexity of the merge fields can increase dramatically in order to get the desired results.

A great resource on how to handle formatting in merge fields is found at this Web page:

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 (312) applies to Microsoft Word 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Word (Word 2007 and later) here: Can't Merge Alphanumeric Data Correctly.

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

Quickly Inserting Table Rows

Need to pop a few extra rows into a table? It is easy to do using the same tools you used to create the table in the first ...

Discover More

Determining the Complexity of a Worksheet

If you have multiple worksheets that each provide different ways to arrive at the same results, you may be wondering how you ...

Discover More

Conditional Page Breaks

Need to have your worksheet printout start on a new page every time a value in a column changes? There are a couple of ways ...

Discover More

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!

MORE WORDTIPS (MENU)

Speeding Up Mail Merges

The Mail Merge tool in Word is a great way to create new, customized documents. If you are doing a lot of merging, you may ...

Discover More

Creating Files with Mail Merge

When you use mail merge to create a document that incorporates all your data source records, you end up with a large document ...

Discover More

Creating a Mail Merge Data File

An easy way to perform a mail merge starts with creating a data file in a Word document. This tip shows how you can create ...

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 for this tip:

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.

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.

Links and Sharing
Share