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

Mail merge with dates, currencies, and other numbers

To merge numbers that have formatting, such as dates, decimals, and currencies, use Dynamic Data Exchange. That way, you’ll get the mail merge results you want and 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

  3. 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.

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

    Select Recipients command

  5. Browse to your spreadsheet, and double-click it.

  6. Click MS Excel Worksheets via DDE (*.xls), and then click OK.

    Note   If you don't see MS Excel Worksheets via DDE (*.xls), select the Show all check box.

    Confirm Data Source dialog box

  7. 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

  3. Click the .csv or .txt file you want, and then click Import.

  4. In the Text Import Wizard, click Next.

  5. 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

  6. 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

  7. Repeat step 6 for each column you want to format.

  8. Click Finish.

Applies To: Word 2016, Word 2013

Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Change language