Mail merge date, number and currency formatting: the fixes that work

A mail merge date format goes wrong (46094 instead of 13 March 2026), and £1,250.50 merges as 1250.5, because Excel stores the raw value and keeps the formatting in the cell. Fix it before 300 invoice reminders go out saying the balance is 1250.5 and due on 46094: in Word with field switches (\@ for dates, \# for numbers), in Excel with a TEXT() column that works in any tool, or in Outlook with SecureMailMerge, which reads the cell format as shown.

Why merged emails show the wrong date or number

In Excel, 13 March 2026 is stored as the number 46094 (days counted from the start of 1900), and £1,250.50 is stored as 1250.5. What you see in the cell is that value passed through a number format such as d mmmm yyyy or "£"#,##0.00.

Word’s mail merge connects to the workbook and reads the stored value, not the formatted display. Microsoft documents this for dates, phone numbers and currency and lists the same symptoms in its troubleshooting article on lost Excel formatting:

In Excel	In the Word merge field	What happened
50%	.5	The percentage format was dropped
$12.50	12.5	Currency symbol and trailing zero lost
07865	7865	The leading zero of a ZIP code vanished
13/03/2026	3/13/2026 or 46094	Date shown in US order, or as a serial number if Word reads the column as numbers

You may also see long decimal tails (such as 0.30000000000000004) on values calculated by formulas, because Word shows the full stored number rather than the rounded one Excel displays.

There are three routes: Word field switches when you merge in Word, an Excel TEXT() column that works in any tool, or SecureMailMerge, which reads the cell format with no setup.

Word field switches

Edit the field code. Word only.

See Fix 1 →

Excel TEXT() column

Finished text in a helper column. Works in any tool.

See Fix 2 →

SecureMailMerge

Reads the cell format as shown. No setup.

See how it works →

Fix 1: Word mail merge date format and number format switches

A Word merge field accepts a formatting switch, a small pattern written inside the field code. \@ sets the mail merge date format and \# sets the mail merge number format. You add switches by editing the field code directly.

Step 1: Show the field codes

Open your merge document and press Alt+F9. Every merge field changes from «PaymentDate» to its underlying code, for example { MERGEFIELD PaymentDate }.

Step 2: Type the switch inside the braces

Click just before the closing brace and type the switch, for example \@ "d MMMM yyyy" for a date or \# "£,0.00" for an amount. Keep the quotation marks around patterns that contain spaces or symbols.

Step 3: Update the field and switch back

Press F9 with the cursor in the field to update it, then press Alt+F9 again to return to the normal view.

Step 4: Preview the results

On the Mailings tab, choose Preview Results and step through several records, including one with a negative amount, an empty cell and a single-digit day, before you finish the merge.

Date switch \@

The date switch is case-sensitive: M is month and m is minutes, so dd/mm/yyyy will print minutes where you expected the month.

{ MERGEFIELD PaymentDate \@ "d MMMM yyyy" }      13 March 2026
{ MERGEFIELD PaymentDate \@ "dd/MM/yyyy" }       13/03/2026
{ MERGEFIELD PaymentDate \@ "MMMM d, yyyy" }     March 13, 2026
{ MERGEFIELD PaymentDate \@ "dddd, d MMMM" }     Friday, 13 March
{ MERGEFIELD PaymentDate \@ "yyyy-MM-dd" }       2026-03-13

Mail merge currency format and number switch \#

In a numeric picture, 0 always prints a digit (padding with zeros), # prints a digit only where there is one, and a comma adds a thousands separator. Currency symbols and % print as typed, and a semicolon separates the positive and negative formats.

The widely copied pattern \# "$#,##0.00" is Microsoft’s own example and works, but Microsoft’s Formula field reference notes that each # with no digit to show prints a space. Dropping the # placeholders, as in \# "$,0.00", gives the same result without that risk, so the examples below use the shorter form.

{ MERGEFIELD Amount \# "0.00" }                  1250.50
{ MERGEFIELD Amount \# ",0.00" }                 1,250.50
{ MERGEFIELD Amount \# "$,0.00" }                $1,250.50
{ MERGEFIELD Amount \# "$#,##0.00" }             $1,250.50 (Microsoft's form)
{ MERGEFIELD Amount \# ",0.00 €" }               1,250.50 €
{ MERGEFIELD Amount \# "£,0.00;(£,0.00)" }       (£1,250.50) for -1250.5

For mail merge decimal places, the number of zeros after the point sets them: "0.00" gives two decimals and rounds the rest.

Mail merge percentage format

The \# switch formats a number but cannot multiply it, so a cell showing 12.5% still merges as .125. Wrap the merge field in a formula field and multiply by 100. Insert each pair of field braces with Ctrl+F9; typed braces do not work.

Cell shows 12.5%, Word merges

.125

Multiply by 100

12.5

Picture 0.0%

12.5%

{ = { MERGEFIELD Rate } * 100 \# "0.0%" }        12.5%

Mail merge leading zeros, ZIP codes and IDs

A mail merge ZIP code that arrives as 2134 instead of 02134 is the classic case. ZIP codes, postcodes, account numbers and phone numbers are identifiers, not numbers, so the cleanest fix is to store them as text: set the column’s format to Text in Excel before you type or paste the values, because Excel drops a leading zero the moment it stores the entry as a number.

Number cell

02134 becomes 2134

Text column first

02134 stays 02134

If the column is already numeric, a 0 placeholder pads the value back to a fixed length:

{ MERGEFIELD Zip \# "00000" }                    02134

The DDE alternative

Word can also read values exactly as Excel displays them through a legacy DDE connection: enable Confirm file format conversion on open under File > Options > Advanced, then pick MS Excel Worksheets via DDE when the wizard asks for the data source. Microsoft documents the steps; test on a copy first.

Fix 2: Format in Excel with TEXT() (works everywhere)

The most portable fix is to do the formatting in Excel. The TEXT() function turns a value into finished text, so every mail merge tool, including Word, Outlook add-ins and CSV exports, receives exactly what you see. Keep the original column for calculations and add a display column next to it. For example a row stores 46094 and 1250.5 while the display columns beside them read 13 March 2026 and £1,250.50, and 46101 shows as 20 March 2026; a negative amount such as -80 can show as (£80.00).

Need	Formula	Result
Long date	=TEXT(A2,"d mmmm yyyy")	13 March 2026
UK short date	=TEXT(A2,"dd/mm/yyyy")	13/03/2026
US date	=TEXT(A2,"mm/dd/yyyy")	03/13/2026
Two decimal places	=TEXT(B2,"0.00")	1250.50
Currency	=TEXT(B2,"£#,##0.00")	£1,250.50
Percentage	=TEXT(C2,"0.0%")	12.5%
Leading zeros	=TEXT(D2,"00000")	02134

Unlike Word, Excel’s TEXT() in English-language Excel uses m for both months and minutes and works out which one you mean from context, so dd/mm/yyyy is correct here. Microsoft’s TEXT function reference lists every format code, and our help page shows a step-by-step TEXT() date example.

Fix 3: Merge from Outlook with SecureMailMerge, no switches or helper columns

Fix 1 only works when you merge in Word, and Fix 2 means adding a helper column for every formatted column. If you send from Outlook, SecureMailMerge reads the text each cell format shows, so the spreadsheet needs no changes. It is a mail merge add-in that runs inside Outlook. Instead of the raw stored value, it reads the text produced by each cell’s number format, so most spreadsheets merge the way they look in Excel with no switches or helper columns.

Excel cell value	Cell format	Merged text
1250.5	"£"#,##0.00	£1,250.50
1250.5	#,##0.00 "€"	1,250.50 €
0.125	0.0%	12.5%
2134	00000	02134
46094	d mmmm yyyy	13 March 2026

A few things to know. The region-dependent long date (marked with * in Excel’s Format Cells dialog) follows your computer’s settings. For predictable results, choose a date format without the * marker, or use a TEXT() column. Very complex custom formats with conditional sections or colour codes may not match, so use a TEXT() column for those. CSV files and Text-formatted cells are read as written, except that numbers in scientific notation are expanded to full digits. One bonus: long numbers in General format, such as phone numbers, merge with their full digits rather than in scientific notation.

Because values arrive as display text, numeric conditions such as {% if OrderTotal >= 100 %} need a column that shows a plain number, so keep an unformatted copy of any column you compare and use the formatted one in the email text. For empty cells, special characters in headers or files that will not load, see default values, Liquid syntax errors and spreadsheet troubleshooting.

Quick-reference table for Word switches and Excel TEXT()

Problem	Word field switch	Excel TEXT()
Date shows as 46094 or US order	\@ "d MMMM yyyy"	=TEXT(A2,"d mmmm yyyy")
Too many decimal places	\# "0.00"	=TEXT(B2,"0.00")
Currency symbol missing	\# "£,0.00"	=TEXT(B2,"£#,##0.00")
Percentage shows as .125	{ = { MERGEFIELD Rate } * 100 \# "0.0%" }	=TEXT(C2,"0.0%")
ZIP code loses leading zero	\# "00000"	=TEXT(D2,"00000")
Negative amounts	\# ",0.00;(,0.00)"	=TEXT(B2,"#,##0.00;(#,##0.00)")

The table covers Fix 1 and Fix 2 only. For SecureMailMerge, see Fix 3 above.

Checklist for SecureMailMerge campaigns with statements, invoices and price lists

• Format every date, amount and percentage column in Excel the way it should read in the email.
• Store ZIP codes, account numbers and phone numbers as Text before entering them.
• Spell out the month (13 March 2026) when recipients live in different countries.
• Keep an unformatted copy of any column you use in a numeric condition, and add a fallback for columns that can be empty.

For the full workflow from spreadsheet to sent campaign, read the complete guide to mail merge in Outlook or the walkthrough on how to mail merge from Excel.

Frequently asked questions

Why does mail merge show my date as a number like 46094?

Excel stores dates as serial numbers, and 46094 is 13 March 2026. Word reads that stored value. Add a date switch such as \@ "d MMMM yyyy" to the field, or merge from a TEXT() column.

How do I keep two decimal places in a mail merge?

In Word, add \# "0.00" (or \# ",0.00" for a thousands separator) to the merge field. In Excel, use =TEXT(B2,"0.00"). SecureMailMerge keeps the decimals shown by the cell’s number format.

How do I show a percentage correctly in mail merge?

Word merges 12.5% as .125. Use a formula field, { = { MERGEFIELD Rate } * 100 \# "0.0%" }, inserting braces with Ctrl+F9, or merge from =TEXT(C2,"0.0%"). SecureMailMerge keeps the cell’s percentage format.

Why do ZIP codes lose their leading zero?

Excel stores 02134 as the number 2134 unless the cell is formatted as Text. Format the column as Text before entering values, pad it in Word with \# "00000", or use the 00000 number format.

Does SecureMailMerge keep my Excel date format?

Yes, in most cases. It reads the formatted value Excel shows. Fixed and most custom formats are preserved. The region-dependent long date follows your computer’s settings, so choose a format without the * marker or use a TEXT() column for a guaranteed result.

Does the fix differ for CSV files?

Yes. A CSV contains plain text with no cell formatting, so values merge as written in the file, apart from numbers in scientific notation, which are expanded. Check the CSV in a text editor, and fix the formatting in Excel before exporting.