Word mail merge · Excel formats

ZIP codes, dates and amounts wrong in a mail merge

Updated

Word's merge reads what Excel stores, not what the cell shows: 06103 arrives as 6103, $12.50 as 12.5 and 50% as .5. Make the ZIP column text in Excel, add a switch to a date or amount field in Word, or connect the workbook another way. Or open the workbook here: each cell prints as Excel shows it.

How the Excel columns become lines

  • ZIP codes keep their zero
  • Dates and amounts as Excel shows them
  • Your list stays on this device
IN EXCELWORD'S MERGEHERE ZIP code06103610306103 ZIP+402105-12342105123402105-1234 Date10/22/19951995-10-22 00:00:0010/22/1995 Amount$12.5012.5$12.50 Word reads what Excel stores, not what the cell shows.

Stored, not shown

What each Excel format turns into in a merge.

Microsoft's explanation: the recipient list shows "the native format in which Excel stores" the data, without the cell's formatting (Microsoft Learn). The Word column is Microsoft's own example for each kind of cell.

Excel cells as Excel shows them, as Word's mail merge gives them, the fix in Word and what this site prints
CellExcel showsWord's merge givesFix in WordHere
ZIP code061036103 Microsoft\# 00000 (five-digit ZIP codes only)06103
ZIP+402105-123421051234 MicrosoftFormat the column as Text in Excel02105-1234
Date10/22/19951995-10-22 00:00:00 Microsoft\@ "MMMM d, yyyy"10/22/1995
Amount$12.5012.5 Microsoft\# $,0.00$12.50
Percent50%.5 MicrosoftConnect by DDE (Windows)50%
  • Switches go inside the field: { MERGEFIELD Renewal \@ "MMMM d, yyyy" } prints October 22, 1995. The M of the month is a capital; a small m means minutes (Microsoft's switches).
  • Show the codes with Alt+F9 on Windows or Option+F9 on a Mac, edit, then show the results again (Microsoft's shortcuts).
  • Here, an .xlsx is read with each cell's own format: the ZIP code's zeros, the date as M/D/YYYY, the dollar sign and the percent, as Excel shows them.

Two fixes before you merge.

One in Excel, for ZIP codes, on Windows and Mac; one in Word on Windows, for every format at once.

In Excel

Make the ZIP column text

Windows
Home > Cells > Format > Format Cells > Number > Text
Mac
Home tab, the Format box: Text

Do it before the ZIP codes go in: a zero Excel already dropped stays dropped, so retype those (Microsoft). Then save the workbook and merge again (Microsoft Learn, method 2; the Mac's Format box).

In Word for Windows

Connect the workbook by DDE

  1. File > Options, Advanced, General: tick Confirm file format conversion on open.
  2. Mailings > Start Mail Merge > Step By Step Mail Merge Wizard; at Select recipients, Use an existing list, then Browse to the workbook.
  3. In Confirm Data Source, tick Show all and choose MS Excel Worksheets via DDE (*.xls), then pick the sheet.

Microsoft says the list then shows "the same formatting that appears in the Excel worksheet" (Microsoft Learn, method 1). Its steps are for Word on Windows.

Do it here

ZIP codes with their zeros back.

Paste the cells or open the .xlsx. A US ZIP code that lost its leading zero gets it back from the state, and a ZIP+4 stored as eight digits prints with its hyphen.

Cells copied from Excel, Google Sheets or Numbers, header row included, or addresses with a blank line between them.

The number on your box

Open the full editor

The sheet shows the sample: a membership list with lost ZIP code zeros. Paste your list or open a file, and it shows yours.

Your list stays on this device. Nothing is uploaded.

Abigail Thornton14 Linden StCambridge, MA 02139Rafael Medina302 Washington StHoboken, NJ 07030Corinne Dubois58 Wooster SqNew Haven, CT 06511Nathan Greer9 Pleasant StConcord, NH 03301Yusuf Adeyemi260 Tremont StBoston, MA 02116-1234Leona Marsh1715 Euclid AveBerkeley, CA 94709
Sample list · 6 labels on 5160

Worked example

Four ZIP codes, their zeros back.

The cells as the spreadsheet lost them, and the labels as the post office reads them.

In your spreadsheet

members-dues.xlsx

4 rows of the sample list
RowNameStreetCityStateZIPRenewalDues
2Abigail Thornton14 Linden StCambridgeMA213910/31/2026$45.00
3Rafael Medina302 Washington StHobokenNJ703011/15/2026$45.00
4Corinne Dubois58 Wooster SqNew HavenCT651112/1/2026$60.00
6Yusuf Adeyemi260 Tremont StBostonMA2116123410/31/2026$45.00
On the 5160 sheet, as listed
  1. Abigail Thornton14 Linden StCambridge, MA 02139
  2. Rafael Medina302 Washington StHoboken, NJ 07030
  3. Corinne Dubois58 Wooster SqNew Haven, CT 06511
  4. Yusuf Adeyemi260 Tremont StBoston, MA 02116-1234
  • 4 ZIP codes lost their first zero in the list and print with it: 02139, 07030, 06511, 02116-1234.
  • A ZIP+4 stored as eight digits prints as five, a hyphen and four.
  • The Renewal and Dues columns stay off the label until you add them to a line in the editor.

Keep every zero in three steps.

  1. Open the workbook here

    The .xlsx as it is: each cell is read with its own number format, so a ZIP code formatted 00000 keeps its zeros.

  2. Check the ZIP codes

    A five-digit US ZIP code that lost its zero, or a ZIP+4 stored as eight digits, is put back from the state on the same row.

  3. Download the PDF

    Print at 100% on plain paper first. The list itself is never uploaded or changed.

Sheets for a membership mailing

The same numbers Word lists under Avery US Letter, with their sizes from Avery's own template files.

Sheets for a membership mailing: size, labels per sheet and the numbers that share each layout
SheetLabel sizePer sheetSame layoutGood for
Avery® 5160 1 × 2-5/8 in 30 8160 · 9160 · 18160 +56 Name, street and city with its ZIP code, 30 to a sheet.
Avery® 5161 1 × 4 in 20 8161 · 5261 · 5909 +19 A long name or a second street line, 4 in wide.
Avery® 5163 2 × 4 in 10 8163 · 18163 · 48163 +29 Dues reminders mailed in large envelopes.
  • A ZIP code outside the US is left as typed: the zeros come back only with a US state or no country.
  • Only the first label filled? That's Update Labels.

Three things that keep 5161 labels straight.

Print at actual size

"Fit to page" can resize the sheet, and then the lower rows drift off their labels. The PDF opens at 100%; keep it there.

Feed the right side up

Printers differ: some print on the side facing up in the tray, others on the side facing down. Mark an X on a plain sheet, print once and look before you load labels.

Test on plain paper

Hold a plain-paper print over a 5161 sheet against a window. If it sits off, calibrate once and save the correction for that printer.

Questions people ask

Why does mail merge drop the leading zero in ZIP codes?

Word reads the number Excel stores, and 06103 is stored as 6103 unless the column is text. Format the ZIP column as Text before typing the codes (Microsoft), or add \# 00000 to a five-digit ZIP field (fix 5).

How do I keep the date format in a mail merge?

Add a date switch to the field: { MERGEFIELD Renewal \@ "MMMM d, yyyy" } prints October 31, 2026. Show the field codes with Alt+F9 (Option+F9 on a Mac) to edit it (each format).

Why do amounts lose their dollar sign in a merge?

Excel stores $12.50 as 12.5 and the merge takes that. A numeric switch, \# $,0.00, puts the sign and the cents back (Microsoft's switches).

What is DDE in a mail merge?

Another way for Word on Windows to read the workbook, which Microsoft says keeps the formatting the worksheet shows. You turn on Confirm file format conversion on open and pick MS Excel Worksheets via DDE (the steps).

Do the Word fixes work on a Mac?

The Text format in Excel and the field switches do: Option+F9 shows the codes. Microsoft gives the DDE steps for Word on Windows.

Will a ZIP+4 print right here?

Yes. Typed as 02116-1234 it prints as typed; stored as 21161234, with a US state on its row, it prints as 02116-1234.

Your workbook, as Excel shows it

The editor reads each cell with its format, puts back lost ZIP code zeros, and lets you add the renewal date to a line.

A ZIP code, a ZIP+4, a date and an amount as Excel shows them, as Word's mail merge receives them, and as this site prints them