How to Preserve Leading Zeros in Excel ZIP Codes for Word Mail Merge
Question details
The user needs to retain leading zeros in ZIP codes within an Excel file used as a data source for Word mail merges.
- Product
- Microsoft Excel / Word
- Device & OS
- not provided
- Scenario
- Setting up a mail merge in Word using an Excel spreadsheet that contains US ZIP codes.
- Observed behavior
- Excel automatically drops leading zeros from ZIP codes (e.g., changing '03498' to '3498'), which then appear incorrectly in the Word mail merge document.
Before starting your mail merge, ensure your data file is saved as an Excel Workbook (.xlsx or .xls) rather than a CSV file, as CSV files often strip out text formatting when reopened.
Format the ZIP Code Column as Text
Change the cell format to Text before entering your ZIP codes to prevent Excel from automatically removing the leading zeros.
By default, Excel treats inputs containing only digits as numbers, which causes it to drop any leading zeros. Formatting the column as Text forces Excel to treat the entries exactly as typed.
Click the column letter at the top of your Excel spreadsheet to select the entire column where you will enter the ZIP codes.
Right-click the selected column and choose 'Format Cells' from the context menu.
In the Format Cells dialog box, go to the 'Number' tab, select 'Text' from the Category list, and click 'OK'.
Type your ZIP codes into the formatted cells. The leading zeros will now be preserved.
Insert an Apostrophe Before the ZIP Code
For a small number of entries, manually adding an apostrophe forces Excel to treat the specific number as text.
Perform Seamless Mail Merges with WPS Office
WPS Office provides a highly compatible and user-friendly environment for managing data and creating mail merges. Easily format your spreadsheets to retain ZIP codes and execute the merge directly in WPS Writer without losing any data integrity.
- 1. Prepare your data in Spreadsheets: Open WPS Spreadsheets, format your ZIP code column as Text, enter your data with leading zeros, and save the file as a .xlsx document.
- 2. Open WPS Writer: Launch WPS Writer and open your letter, label, or envelope template. Navigate to the 'Mailings' tab on the top ribbon.
- 3. Connect the data source: Click 'Open Data Source' and browse to select the .xlsx file you just saved.
- 4. Insert merge fields: Click 'Insert Merge Field' and select your ZIP code column header to place it into your document. The leading zeros will be perfectly retained.
- 5. Finish the merge: Click 'Merge to New Document' or 'Merge to Printer' to generate your final merged files.
Frequently Asked Questions
Why does Excel remove leading zeros from my ZIP codes?
Excel is designed to perform mathematical calculations, so it automatically recognizes inputs containing only digits as numeric values. In mathematics, a leading zero has no value, so Excel strips it out. Formatting the cells as Text overrides this behavior.
Can I format existing ZIP codes that have already lost their leading zeros?
Yes. You can use Custom formatting to add them back visually. Select the cells, open the Format Cells dialog, choose 'Custom', and type '00000' in the Type box. This forces the display to always show 5 digits, restoring the leading zeros.
Does saving my Excel file as a CSV cause issues with mail merge?
Yes, it is highly recommended to avoid CSV files for mail merges if you have special formatting like leading zeros. CSV files do not store cell formatting. Even if you format a CSV column as Text, saving and reopening it or linking it to Word may cause the leading zeros to be dropped again. Always save as an .xlsx or .xls workbook.
