Mail Merge for 1,450 Scholarship Letters
Each February the alumni office at a 14,000-student university produces 1,450 scholarship renewal letters by merging an Excel workbook into a Word letter. The workbook has one header row and 1,450 data rows; the Award Amount column was pasted from a finance system and is stored as text with leading apostrophes, and the Award Date column is formatted in Excel as 'March 4, 2025.' Sixty-two recipients have no Address 2 value, and the letter must omit that line entirely rather than leave a blank. A conditional sentence must appear only for the 118 Dean's List recipients. The office also found 38 duplicate rows after combining two appeal lists. When the merge runs, it stops at record 812 with 'Word could not finish merging the document,' and the few letters produced show dates as 3/4/2025 and award amounts as 0012500. The team needs a repeatable process for next year, not a one-time cleanup.
1. Write the field logic that suppresses Address 2 when it is blank and prints the Dean's List sentence only for the 118 flagged recipients. Explain why a plain merge field leaves a blank line.
Read the worked answer
Use IF fields built with Ctrl+F9; braces typed from the keyboard are not field delimiters.
Address 2:
{ IF { MERGEFIELD Address_2 } <> "" "{ MERGEFIELD Address_2 }
" "" }
The paragraph mark must sit inside the true-text result. A plain { MERGEFIELD Address_2 } always occupies its paragraph, so when the value is empty the paragraph remains and you get a blank line.
Dean's List:
{ IF { MERGEFIELD Deans_List } = "Yes" "Congratulations on qualifying for the Dean's List. " "" }
Match the source exactly. If Excel stores TRUE/FALSE or 1/0, compare to "TRUE" or use the appropriate test; text comparison is literal, so 'yes' will not match 'Yes.' Insert the outer field with Mailings > Rules > If…Then…Else and nest the merge field inside, or type the inner field with Ctrl+F9. Preview several records, including one with blank Address 2 and one non-Dean's record, before merging.
2. Dates print as 3/4/2025 and award amounts as 0012500. What causes this, and what switches or source fixes do you apply?
Read the worked answer
Merge fields carry the source value, not the Excel cell's display format. Word applies its own default formatting unless you add a switch.
- Date:
{ MERGEFIELD Award_Date \@ "MMMM d, yyyy" }produces March 4, 2025. Use the switch on the merge field, and make sure Word recognizes the source as a date; if the column is text, convert it in Excel first. - Amount:
{ MERGEFIELD Award_Amount \# "#,##0.00" }produces 12,500.00. The leading apostrophe in Excel forces the value to text, so a numeric picture switch may be ignored or may produce unexpected results. Remove the apostrophes at the source: select the column, Data > Text to Columns > Finish, or use VALUE() in a helper column, then point the merge at the numeric column. - To keep the formatting when you edit the field, add
\* MERGEFORMAT, but do not rely on it to fix text-typed numbers.
Best practice: clean the data source and use a separate MergeReady sheet with real dates and numbers; then the field switches behave predictably and the process is repeatable next February.
3. The merge stops at record 812, and 38 duplicate rows remain. How do you isolate the failure and make deduplication repeatable without hand-editing 1,450 rows?
Read the worked answer
Isolate first; do not rerun the whole merge repeatedly.
- Use Mailings > Edit Recipient List > Filter to narrow to records 800–820, or sort by a unique ID and merge that range. If the failure follows a specific record, inspect its fields for unmatched quotation marks, stray line breaks, a malformed hyperlink, or a value the Excel driver has inferred as the wrong type.
- A common cause is mixed data types in a column: the OLE DB provider samples rows and can return nulls or truncate values. Convert the source to a clean table with consistent types, or export it to CSV and use the CSV as the data source.
- After fixing, merge the narrowed range, then run the full 1,450.
For deduplication, define a composite key such as Email + Award_Year. In Excel, use Remove Duplicates on that key, or Power Query with a Remove Duplicates step and a filter for the 118 Dean's List records. Keep the cleaned query output as the merge source. In Word, use Edit Recipient List to exclude any remaining duplicates before merging, but treat that as a check, not the record of truth. Save the query and document so next year is a refresh, not a manual cleanup.