You've built a list of contacts and other data that you want to use for a Word mail merge. If your data source is an existing Excel spreadsheet, then you just need to prepare the data for a mail merge. But if your data source is a tab delimited (.txt) or a comma-separated value (.csv) file, you first need to import the data into Excel, and then prepare it for a mail merge.
Step 1: Set up your data source in Excel
If you're using an Excel spreadsheet as your data source for a mail merge in Word, skip this step. If the data source is a .txt or a .csv file, import it into Excel.
After you successfully import a .txt or .csv file, go to Step 2.
-
Open Excel.
-
Go to Data > From Text/CSV.
-
Select the .csv or .txt file.
-
In the import preview, set the ZIP/postal code column to Text.
-
To complete the import, select Load (or Import) in the import preview. Excel then inserts the data into a worksheet.
-
Use this sheet as the data source for your mail merge.
-
Open Excel.
-
Excel 2016Â Â Â Go to Data > Get External Data > From Text.
-
Choose the .txt or .csv file you want, and then select Import.
-
In the Text Import Wizard, in the Original data type pane, choose Delimited.
-
Select My data has headers above the Preview pane if the file has headers, and then choose Next.
-
In the Delimiters pane, select the check box that matches the delimiter (such as a Tab or Comma) the data uses, and then choose Next.
-
Under Data preview, choose the column that contains ZIP Codes or postal codes, and under Column data format, choose Text.
Note: Each time you apply a data format—General, Text, or Date—to a column, the name of the format appears in the table header for that column.
-
Repeat step 7 as necessary, choosing the column you want to change and the data format you want to apply.
-
Choose Finish.
-
In the Import Data dialog box, accept the default setting of Existing worksheet and the cell address, and then choose OK.
Caution:Â The cell address in the Import Data dialog box shows the currently selected cell. Data is imported beginning with the named cell address.
-
Save your spreadsheet with a new file name.
Step 2: Prepare your data source
In your Excel data source that you'll use for a mailing list in a Word mail merge, make sure you format columns of numeric data correctly. Format a column with numbers, for example, to match a specific category such as currency.
If you choose percentage as a category, be aware that the percentage format will multiply the cell value by 100. Format a column of percentages as text if you want to avoid that multiplication factor.
ZIP Codes or postal codes needs to be formatted as text to preserve the data during a mail merge. If not done when imported, format it now. Leading zeros—for example, 00399—in codes are dropped during a mail merge if they're not formatted as text.
-
Choose the column that contains the ZIP Codes, postal codes, or other data to be formatted.
-
Go to Home, and in the Number group, select the Number Format box down arrow, and then choose an option in the list (such as Text).
Make sure you add the appropriate symbol before or after a merge field. For example, here's how the currency and percentage values look if you omit symbols.
If you include the symbols, the numbers make more sense.
In your mail merge document, you add the symbols before or after the merge fields like this:
See also
If you've built a contact list in an Excel spreadsheet, it's important to format any zip codes or postal codes as text to avoid losing data. If you're importing into a new spreadsheet any contacts from either a text (.txt) or a comma-separated value (.csv) file, the Text Import Wizard can help you import and format your data.
Step 1: Set up your data source in Excel
If you're already using an Excel spreadsheet as your data source for a mail merge in Word, go to Step 2 in this topic. If the data source is a .txt or a .csv file, use the Text Import Wizard to set up your data in Excel.
-
Open Excel.
-
On the Data tab, choose From Text (Legacy).
-
Select the .csv or .txt file.
-
In the Text Import Wizard: ​​​​​​​Step 1: Select Delimited.
Step 2: Choose the correct delimiter (for example, Comma).
Step 3: Select the ZIP/postal code column and set Column data format to Text.
-
Click Finish.
-
In the Import Data dialog box, choose where you want Excel to put your data, and then select OK.
-
Save the spreadsheet with a new file name.
-
Use this sheet as the data source for your mail merge.
Step 2: Format numerical data in Excel spreadsheet
To ensure your zip code or postal code pass through a mail merge without losing any zeros, format the column containing those codes as text.
-
Open your spreadsheet and select the column that contains the zip codes or postal codes.
-
On the Home tab, in the Format box, choose Text.
Now, you can use this data for a mail merge.
