Microsoft KB Archive/832286

= How to save the addresses from a Word mail-merge label document to Excel =

Article ID: 832286

Article Last Modified on 1/20/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition

-



SUMMARY
This article describes how to save addresses that are contained in a Microsoft Word mail merged label document to a Microsoft Excel workbook.



MORE INFORMATION
The following steps assume that you already have an existing Word mail merged label document that contains the addresses that you want to save in an Excel workbook. To save addresses that are contained in a Word mail merged label document to an Excel workbook, follow these steps, as appropriate for the version of Excel that you are running.

Microsoft Office Excel 2007
 Start Microsoft Office Word 2007, and then open the Word document that contains your merged address labels. Click the Home tab. In the Editing group, click Replace. On the Replace tab, click More. With the insertion point in the Find what box, click Special, and then click Section Break.

You should see ^b in the Find what box. Leave the Replace with box blank, and then click Replace All. Click Close to close the Find and Replace dialog box.</li> Under Table Tools, click the Layout tab.</li> In the Table group, click View Gridlines.</li> Delete all blank columns in your merged address label document.

To do this, click inside a blank column, click Delete in the Rows & Columns group, and then click Delete Columns.

Note Repeat this step until each blank column is deleted in the address labels table.</li> Replace all paragraph marks with a tab. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> Click the Microsoft Office Button, and then click Word Options.</li> Click Display.</li> Click to select the Show all formatting marks check box, and then click OK.</li> Click the Home tab.</li> In the Editing group, click Replace.</li> On the Replace tab, click More.</li> On the Replace tab, delete the contents of the Find what box.

With the insertion point in the Find what box, click Special, and then click Paragraph Mark.</li> Delete the contents of the Replace with box.

With the insertion point in the Replace with box, click Special, and then click Tab Character.</li> Click Replace All.</li> Click Close.</li></ol> </li> Click the Layout tab.</li> With the insertion point in the table, click Convert to Text in the Data group.</li> <li>In the Convert Table to Text dialog box, click Paragraph marks, and then click OK.</li> <li>Click the Microsoft Office Button, and then click Save As.</li> <li>In the Save As dialog box, change the Save as type box to Plain Text (*.txt).

Type a new name for your address list in the File name box, and then click Save.</li> <li>If a File Conversion dialog box appears, click Windows (Default), click to select the Insert line breaks check box, make sure that the End lines with box has CR/LF selected, and then click OK.</li> <li>Close your new file, and then exit Word.</li> <li>Start Excel 2007.</li> <li>Click the Microsoft Office Button, and then click Open.

Change the Files of type box to All Files (*.*), and then open the file that you saved in step 16.</li> <li>When the Text Import Wizard starts, accept the default settings, and then click Finish.</li></ol>

Microsoft Office Excel 2003 and earlier versions of Excel
<ol> <li>Start Word, and then open the Word document that contains your merged address labels.</li> <li>On the Edit menu, click Replace.</li> <li>On the Replace tab, click More.</li> <li>With the insertion point in the Find what box, click Special, and then click Section Break.

You should see ^b in the Find what box.</li> <li>Leave the Replace with box blank, and then click Replace All.</li> <li>Click Close.</li> <li>On the Table menu, click Show Gridlines.</li> <li>Delete all blank columns in your merged address label document.

To do this, click inside a blank column, on the Table menu, point to Select, and then click Column.</li> <li>On the Table menu, point to Delete, and then click Columns.</li> <li>Repeat steps 8 and 9 for each blank column in your merged label document that you want to remove.</li> <li>Replace all paragraph marks with a tab. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>On the Tools menu, click Options.</li> <li>On the View tab, click to select the All check box, and then click OK.</li> <li>On the Edit menu, click Replace.</li> <li>Click More to expand the Replace tab.</li> <li>On the Replace tab, delete the contents of the Find what box.

With the insertion point in the Find what box, click Special, and then click Paragraph mark.</li> <li>Delete the contents of the Replace with box.

With the insertion point in the Replace with box, click Special, and then click Tab Character.</li> <li>Click Replace All.</li> <li>In the Find and Replace dialog box, click Close.</li></ol> </li> <li>With the insertion point in your Word table, point to Convert on the Table menu, and then click Table to Text.</li> <li>In the Convert Table to Text dialog box, click Paragraph marks, and then click OK.</li> <li>On the File menu, click Save As.</li> <li>In the Save As dialog box, change the Save as type box to Plain Text (*.txt) (Text Only (*.txt)).

Type a new name for your address list in the File name box, and then click Save.</li> <li>If a File Conversion dialog box appears, click Windows (Default), click to select the Insert line breaks check box, make sure that the End lines with box has CR/LF selected, and then click OK.</li> <li>Close your new file, and then exit Word.</li> <li>Start Excel.</li> <li>On the File menu, click Open.

Change the Files of type box to All Files (*.*), and then open the file that you saved in steps 14, 15, and 16.</li> <li>When the Text Import Wizard starts, accept the default settings, and then click Finish.</li></ol>

Additional query words: word 2000 2002 2003 wd2000 wd2002 wd2003 excel 2000 xl2000 xl2002 xl2003 office 2000 2002 xp 2003 mail merge list database extract unmerge

Keywords: kbexpertisebeginner kbmerge kbemail kbhowto KB832286

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.