Format mail merge numbers, dates, and other values in Excel

To make sure your ZIP or postal codes come through a mail merge without losing any zeros, format the ZIP or postal code column as text.

  1. In your Excel spreadsheet, select the column you want to format.
  2. Right-click, and then click Format Cells.
  3. On the Number tab, click Text.

Text option selected

If your spreadsheet includes dates, decimals, monetary amounts, or other numbers, you can use Dynamic Data Exchange to make sure those values come through the merge without losing the formatting in your Excel spreadsheet.

If you’re just starting out and want to import addresses from a .csv or .txt file, your best bet is to use the Text Import Wizard.

Mail merge with dates, currencies, and other numbers

To merge numbers that have formatting, such as dates, decimals, and currencies, use Dynamic Data Exchange. With Dynamic Data Exchange, you’ll get the mail merge results you want, and you keep the formatting of your Excel spreadsheet. Here’s how:

  1. In Word, click File > Options > Advanced.
  2. Scroll to the General section, and select the Confirm file format conversion on open check box.

Confirm file format conversion on open option

  1. Click OK.

After you start your mail merge and choose the kind of merge you want to run, it’s time to connect to your data list. Because Dynamic Data Exchange is turned on, you’ll receive a few extra prompts.

  1. In the mail merge document, click Mailings > Select Recipients > Use an Existing List.

Select Recipients command

  1. Browse to your spreadsheet, and double-click it.
  2. Click MS Excel Worksheets via DDE (*.xls), and then click OK.

Confirm Data Source dialog box

  1. If you don't see MS Excel Worksheets via DDE (*.xls), select the Show all check box.
  2. Click Entire Spreadsheet, and then click OK.

Microsoft Excel dialog box in Word

Now continue with your email, label, or document merge.

 Tip    To prevent being prompted by Word every time you open a data file, you can open Word Options again and clear the Confirm file format conversion on open check box after you connect to your mailing list.

Import an address list

If you want to use information from a .txt or .csv file (for example, if you exported your Hotmail contacts into a .csv file), import that information into Excel by using the Text Import Wizard. Here’s how:

  1. Start Excel.
  2. Click Data > From Text.

Excel  Ribbon Image

  1. Click the .csv or .txt file you want, and then click Import.
  2. In the Text Import Wizard, click Next.
  3. In step 2, click the delimiter that your data uses (the Data preview window shows you what the results will look like).

Text Import Wizard Step 2

  1. In step 3, click the column that has postal codes, dates, currencies, or other values, and then click Text under Column data format.

Text Import Wizard Step 3

  1. Repeat step 6 for each column you want to format.
  2. Click Finish.
 
 
Applies to:
Word 2013