by Connie Miller

Your mail merge appears to be working just fine, but suddenly you notice that prices, dates and other numbers don't look right in your merged publications. Read on to learn how to avoid this particularly costly problem.

Question from your comments

My data is in an Excel worksheet. When I do a mail or catalog merge, the prices in the Price column don't retain their appropriate money formats. Help!

Advice from the Answer Box

Little frustrating things like numbers not merging right can mess up an entire mail or catalog merge project.

Fortunately, you can use a few different approaches to get any numbers — currency, percentages, dates, zip codes with initial zeros — to come out looking just the way you want. The table below lists the approaches and gives my advice about when to use them. Following the table, you'll see more specifics on exactly how each approach works.

Save a copy of your Excel worksheet as a text file

If your worksheet contains a lot of numbers or if you've spent a lot of time entering numbers in a specific format, you certainly don't want to reformat everything just to make mail or catalog merge work. That's why saving a copy of your Excel worksheet as a text file is such a great option.

Numbers don't look right after you merge data from an Excel worksheet because the data is transferred in the native format in which Excel stores it rather than with the formatting applied in the worksheet cells. If you save the Excel worksheet as a text (.txt) file, you save formatted numbers not the raw data. If you connect to the text file when you merge, numbers will look exactly the way you want them to in the merged publications.

To save an Excel worksheet as a text file

1. With the worksheet displayed that you want to save, on the Excel File menu, click Save As.
2. For Save as type, click Text (tab delimited) (*.txt).
3. If you get prompted to save only the active worksheet, click OK. When you get warned that the text file may contain features that are not compatible with Text (tab delimited), click Yes.
4. Close the text file you just saved.
5. In Publisher, when you're doing a mail or catalog merge and you choose to connect to the text file you just saved, you'll see the Text File Connection Parameters dialog box. Select Tab as the separator character, and then click OK.

Format cells in the Excel worksheet as text

This approach might be just the ticket if you're creating a new Excel worksheet to use with a mail or catalog merge project. It's a very straightforward approach because you can set up your dates and percentages and other numbers in your worksheet just the way you want them to look in Publisher after you merge, and every time you merge they'll look that way.

Number formatting problems in a mail or catalog merge occur when the cells containing the numbers in the Excel data file are formatted as Number, Currency, Date, Percentage, and so on. So, the solution for getting numbers to behave properly when you merge is to format the cells that contain numbers as Text. When cells are formatted as Text, you can type your numbers exactly the way you want them to look (with dollar signs, zeros, etc.), and they will merge exactly as you type them.

To apply cell formatting in Excel, select the column that contains the numbers, and then click Cells on the Format menu. On the Numbers tab, click Text.

Good tip about initial zeros on zip codes

You don't actually have to format cells containing zip codes as Text to make zip codes with initial zeros merge correctly. Just type an apostrophe before each initial zero. The apostrophe doesn't show up in the cell. It just makes the zeroes behave.

Wishing you many happy merges,

Connie, the Publisher Answer Box monitor

About the Author

Connie Miller, a writer on the Microsoft Office User Assistance team, collects and responds to questions and suggestions you submit to the Publisher Answer Box. To submit a detailed question of your own that we may be able to respond to, click Feedback.