Wednesday, October 27, 2021

Prepare your excel data source for a word mail merge

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, use the Text Import Wizard to set up your data in Excel.

After you successfully import a .txt or .csv file, go to Step 2.

  1. Open Excel.

  2. Go to Data > From Text/CSV.

    Selecting Text/CSV from the Data tab

  3. Choose the .txt or .csv file you want, and then select Import.

  4. In the preview window, select Transform Data.

  5. Select the ZIP, Postal Code, or other column to format.

  6. Go to Transform > Data Type: and select Text.

    Power Query window with Text selected

  7. Select Replace current.

  8. Repeat steps 5 - 7 as needed.

  9. Select Close & Load.

  10. Go to File > Save as and save as with a different name.

  1. Open Excel.

  2. Excel 2016    Go to Data > Get External Data > From Text.

    The From Text option is highlighted on the Data tab.

  3. Choose the .txt or .csv file you want, and then select Import.

  4. In the Text Import Wizard, in the Original data type pane, choose Delimited.

    Excel Get External Data from Text, Text Import Wizard, Step 1 of 3

  5. Select My data has headers above the Preview pane if the file has headers, and then choose Next.

  6. 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.

    The options for Delimiters are highlighted in the Text Import Wizard.

  7. Under Data preview, choose the column that contains ZIP Codes or postal codes, and under Column data format, choose Text.

    The Text option for Column data format is highlighted in the Text Import Wizard.

    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.

  8. Repeat step 7 as necessary, choosing the column you want to change and the data format you want to apply.

  9. Choose Finish.

  10. In the Import Data dialog box, accept the default setting of Existing worksheet and the cell address, and then choose OK.

    In the Import Data dialog box, choose to put the data in an existing worksheet, the default setting, or in a new worksheet

    Caution: The cell address in the Import Data dialog box shows the currently selected cell. Data is imported beginning with the named cell address.

  11. 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.

  1. Choose the column that contains the ZIP Codes, postal codes, or other data to be formatted.

  2. 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).

    In Excel, on the Home tab, in the Number group, choose the down arrow in the General box to choose the number format to use.

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.

Mail merge document that reads "your contribution of 50.00" and "offer you a 20 discount."

If you include the symbols, the numbers make more sense.

Mail merge results document reads "your contribution of $50.00" and "offer you a 20% discount."

In your mail merge document, you add the symbols before or after the merge fields like this:

Sample mail merge document where a field named Donation is preceded by a dollar sign and a field named Percent is followed by a percent sgn.

See also

An essential step in a Word mail merge process is setting up and preparing a data source. You can use an existing Excel data source or build a new one by importing a tab-delimited (.txt) or comma-separated value (.csv) file. After you've set up and prepared your data source, you can perform a mail merge by using Dynamic Data Exchange (DDE) with the Step-by-Step Mail Merge Wizard or by using a manual mail merge method.

If you're not using an existing Excel data source for your mail merge, you can use a contact list or an address book in a .txt or .csv file. The Text Import Wizard guides you through the steps to get data that's in a .txt or .csv file into Excel.

  1. Open Excel.

  2. On the Data tab, choose From Text/CSV.

  3. Choose the .txt or .csv file you want, and then choose Import.

  4. In the Text Import Wizard, in the Original data type pane, choose Delimited.

    Excel Get External Data from Text, Text Import Wizard, Step 1 of 3

  5. Select the My data has headers check box above the Preview pane if the file you're importing has headers, and then choose Next.

  6. In the Delimiters pane, select the check box that matches the delimiter (such as a Tab or Comma) your data uses, and then choose Next.

    The options for Delimiters are highlighted in the Text Import Wizard.

    Tip: The Data preview pane shows what the results will look like in a table format.

  7. Under Data preview, choose the column that contains ZIP Codes or postal codes, and under Column data format, choose Text.

    The Text option for Column data format is highlighted in the Text Import Wizard.

    Notes: 

    • The available formats are General, Text, and Date. Each time you apply a data format to a column, the name of the format appears in the table header for that column.

    • A column that contains numerical data such as a percentage or a currency value, for example, can only be formatted after the file is imported. See the "Format numerical data in Excel" subsection that follows for more information.

  8. Repeat step 7 as necessary, choosing the column you want to change and the data format you want to apply.

  9. Choose Finish.

  10. In the Import Data dialog box, accept the default setting of Existing worksheet and the cell address, and then choose OK.

    In the Import Data dialog box, choose to put the data in an existing worksheet, the default setting, or in a new worksheet

    Caution: The cell address in the Import Data dialog box shows the currently selected cell. Data is imported beginning with the named cell address.

  11. When done, save your data source with a new file name.

Format numerical data in Excel

Format any numerical data like percentages or currency values in any new or existing data source in Excel that you intend to use in a Word mail merge. To preserve numeric data you've formatted as a percentage or as currency during a mail merge, follow the instructions in the "Step 2: Use Dynamic Data Exchange (DDE) for a mail merge" section.

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.

  1. Open your Excel data source.

  2. Choose a column that you want to format.

  3. On the Home tab, in the Number group, choose the Number Format (General) box down arrow, and then choose an option in the list.

    In Excel, on the Home tab, in the Number group, choose the down arrow in the General box to choose the number format to use.

  4. Repeat steps 2 and 3 as necessary.

  5. When done, choose Save.

When you have an Excel data source that contains formatted numbers like percentages and currency values, some of the numeric data may not retain its formatting during a mail merge. You'll get the mail merge results you want and can preserve the format of your numeric data in Excel by using DDE.

Excel data format compared to Work Merge Field by using or not using Dynamic Data Exchange

Note: If you decide not to use DDE, follow the guidance in Use mail merge to send bulk email messages.

  1. Open Word, choose File > Options > Advanced.

  2. Under General, select the Confirm file format conversion on open check box.

    Confirm file format conversion on open option

  3. Choose OK.

  4. On the Mailings tab, choose Start Mail Merge > Step by Step Mail Merge Wizard.

    In Word, on the Mailings tab, choose Start Mail Merge, and then choose Step by Step Mail Merge Wizard

  5. In the Mail Merge pane, under Select recipients, choose Use an existing list.

    In Word, the Mail Merge task pane that opens when you choose the Step by Step Mail Merge Wizard in the Mail Merge group

  6. Under Use an existing list, choose Browse, and then open your formatted spreadsheet.

  7. In the Confirm Data Source dialog box, select the Show all check box, and choose MS Excel Worksheets via DDE (*.xls) > OK.

    Confirm Data Source dialog box

  8. In the Microsoft Excel dialog box, choose Entire Spreadsheet > OK.

    Microsoft Excel dialog box in Word

  9. When the Mail Merge Recipients dialog box opens, choose OK.

    Mail Merge Recipients dialog box that shows the contents of a Excel spreadsheet used as a data source for a mailing list

  10. Continue with your letter, email message, label, or envelope merge.

Tip: You can prevent being prompted by Word every time you open a data file. After you connect to your mailing list, go to File > Options > Advanced. Under General, clear the Confirm file format conversion on open check box.

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 that contains your Gmail contacts, for example, use the Text Import Wizard to set up your data inExcel.

  1. Open Excel, and on the Data tab, choose From Text.

    On the Data tab, select From Text

  2. Choose the .csv or .txt file you want, and then choose Get Data.

  3. In the Text Import Wizard, choose Next.

  4. In Delimiters, check the box that matches the delimiter that separates each element of your data (such as a tab or comma). Then choose Next.

    Tip: The Preview of selected data pane shows you what the results will look like in a table format.


    Step 2 of the Text Import Wizard
     

  5. In Preview of selected data, choose the column that containing the zip codes or postal codes. Then, in Column data format, choose Text.

    Text Import Wizard step 3
     

  6. Repeat step 5 as necessary, choosing the column you want to change and the data format you want to apply.

    Note: Each time you apply a data format to a column, the name of the format appears in the table header for that column.

  7. Click Finish.

  8. In the Import Data dialog box, choose where you want Excel to put your data, and then click OK.

    Note: Save your spreadsheet with a new file name.

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.

  1. Open your spreadsheet and select the column that contains the zip codes or postal codes.

  2. On the Home tab, in the Format box, choose Text.

    On the Home tab, in the Format box, select Text

Now, you can use this data for a mail merge.

1 comment:

  1. Thanks for giving the information. This blog is very helpful. I learned a lot from this I hope you will keep sharing such information in the future also.
    visit site

    ReplyDelete