Word mail merge changed your Excel date and number formats
Reformatting the spreadsheet hasn’t fixed the letters. Can you correct the whole batch without editing each recipient’s copy?
When the same error appears across a batch of letters, correcting the source list or Word template lets you generate the batch again. Start with one affected recipient: an amount missing its currency symbol may only need formatting, while an identifier missing leading zeros needs its original characters restored. These instructions use the Windows desktop versions of Microsoft 365 and Word 2024.
In one Microsoft Q&A report, every field merged correctly except a date that appeared as a number. The author later reported that closing Excel before opening the Word document resolved that case.
Which file needs the correction?
Microsoft’s troubleshooting article explains that Word can receive the value Excel stores without the worksheet cell’s formatting. A percentage may arrive as a decimal, for example. Reapplying the same cell format does not necessarily change what Word receives.
On a copy of the list, compare one incorrect field with what that recipient should see. For example, the amount 12500 can be formatted as $12,500.00 if dollars are the intended currency. The identifier 427 needs a different check: it could be a complete code or the remainder of 00427 after zeros were lost. The examples below show where to make each correction.
| Required output | Question about the source | Where to work |
|---|---|---|
| Identifier 00427 | Is 00427 stored as text, or is 427 displayed with extra zeros? | The identifier column in Excel |
| 2026-11-06 | Does the source actually mean November 6? | A text output column or Word date field, once the date is correct |
| $12,500.00 | Is 12500 the correct amount in dollars? | The Word numeric field and currency label |
Do not turn the entire workbook into text to repair one letter. A list may also support calculations or date sorting. Keeping the working values alongside a clearly named output column lets the same record support both jobs.
Keep identifiers intact before merging
For a new list, select the empty identifier column, press Ctrl+1, and choose Text before entering or pasting the codes. Microsoft’s text-number guidance describes this approach. Codes can consist entirely of digits while still needing to retain their characters exactly.
For a CSV, use Data → From Text/CSV and assign the identifier column a text type during import. The mail merge preparation guide specifically addresses postal codes. If the initial preview does not expose column types, open Transform Data and inspect the conversion there.
When zeros have already disappeared, changing the column to Text does not reveal how many were lost. Compare with the original file. In Power Query, converting a number back to text after a numeric conversion will not recover those characters; apply the text type while the original string is still intact. See Microsoft’s leading-zero guidance for the import workflow.
If an internal code is definitely five digits and 427 is its correct numeric portion, a helper formula such as =IF(A2="","",TEXT(A2,"00000")) produces 00427 while leaving an empty source blank. The TEXT function generates the formatted string. A five-digit rule must not be applied indiscriminately to international postal codes, telephone numbers, or identifiers of different lengths.
For example, a list containing both 427 and AB427 does not have a uniform numeric identifier scheme. Preserve the verified original codes instead of forcing them into one mask. Keep a missing identifier empty and investigate it; a manufactured 00000 can look like a valid entry during a quick review.
Give the letter its own date column
Use a separate column such as LetterDate when several templates need the same date wording. Microsoft documents converting dates to text with TEXT. If B2 contains the correct Excel date, this illustrative formula creates a year-month-day output while leaving an empty source blank:
=IF(B2="","",TEXT(B2,"yyyy-mm-dd"))The IF blank check prevents an empty source from being formatted as a date. A source date of November 6, 2026 produces 2026-11-06. Replace B2 with the appropriate reference and fill the formula through the last recipient. Insert LetterDate into Word rather than the original date field. Continue using the original column for calculations such as days remaining before a deadline.
A date-looking string needs more investigation. In a separate Q&A discussion, a user expected 02/09/2021 to mean September 2, but the letter showed February 9. The author subsequently identified the source entries as strings beginning with an apostrophe. A different display pattern cannot establish which day the original author intended.
Confirm the input convention with the list owner before converting ambiguous strings. Include both a date with a day above 12 and a date whose month and day could be reversed in your comparison. If different rows use different conventions, a single conversion rule can produce a list that is only partly correct.
Format a date or amount in Word
When Word receives the right date or number and only the presentation needs changing, format the merge field in the template. Insert the field first, then press Alt+F9 to display its code. Microsoft’s merge-field examples use \@ for dates and \# for numbers.
{ MERGEFIELD DueDate \@ "yyyy-MM-dd" }
{ MERGEFIELD Amount \# "$#,##0.00" }DueDate and Amount are sample column names. Retain your actual field name and add the format inside the existing field. Typing ordinary braces around text does not create a merge field. The amount example assumes dollar values and two decimal places; choose the unit and precision your document requires.
The current Word field-format reference distinguishes uppercase M for months from lowercase m for minutes. Numeric separators follow regional settings. Also, \* MERGEFORMAT preserves character formatting; it is not an instruction to import Excel’s currency or date format.
Press Alt+F9 again, select the edited field, and press F9 to update it. The Word shortcut reference lists code display and field updating as separate actions. Use Preview Results to move between recipients. If a date still arrives as a number, inspect the source and consider the text output column above. Adding more switches will not repair a wrongly interpreted day.
Choose where to maintain the wording according to the actual documents. A dollars-only letter and a letter that spells out USD can share the same numeric source but have different Word templates. A single date phrase used by several templates may be easier to maintain in one Excel output column.
Point the template at the corrected column
Save the workbook and close it before reconnecting. For a new letter, use Mailings → Start Mail Merge → Letters, then Select Recipients → Use an Existing List. Select the saved workbook and appropriate sheet. Microsoft’s Excel mail merge workflow calls for completing spreadsheet changes before connecting it.
A new output column does not redirect an existing field. Select the old date field, remove it, and insert LetterDate using Mailings → Insert Merge Field. The field insertion guide explains how to choose a column from the connected source. Do not just type the displayed angle brackets and field name.
If LetterDate is missing from the list, check the file path and sheet before renaming columns again. You may have saved the new workbook while Word still points to last month’s copy. After reconnecting, verify that formulas reach the last recipient and remain aligned with the correct names. Sorting only the output column can attach a correctly formatted date to the wrong person.
Review recipients before producing the final batch
Turn on Mailings → Preview Results. Microsoft’s preview controls let you move between records or find a particular recipient. Select cases from the actual list: a leading-zero code, an empty date, an ambiguous month/day combination, and a large amount. Compare each with the same person’s source record.
Once the preview is correct, choose Finish & Merge → Edit Individual Documents. The personalized-letter workflow includes this option for reviewing the generated letters before printing. Check long names, line wrapping, and the last recipient as well as date and number formatting.
If the same error appears throughout the batch, return to the template or source column and merge again. A one-person edit in the generated document does not become a reusable template change. Save the template and this mailing’s output under distinct names so the next run starts from the corrected fields.