Microsoft KB Archive/819964

= How to convert a single column of addresses to a CSV text file in Excel =

Article ID: 819964

Article Last Modified on 5/18/2007

-

APPLIES TO


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

-



SUMMARY
This article describes how to convert a single column of addresses in a Microsoft Excel worksheet into a comma-separated value (CSV) file that you can import into another program (for example, Microsoft Word).

Note For the address example in this article, the Excel worksheet contains the following address information:   A1: Jane Clayton A2: Microsoft A3: 456 Elm Street A4: Sometown, USA 67890 A5: A6: Jose Saraiva A7: 789 Oak Road A8: Mytown, USA 54321



MORE INFORMATION
If a Microsoft Excel worksheet contains address data in a single column (for example, in column A), and you want to convert the address data into a CSV text file, follow these steps.

Edit the Excel Worksheet
 Open your Excel workbook that contains the address data.  In the address example, one address contains four rows and the second address contains only three rows. Additionally, each address set is separated by a single row.

To successfully convert your address data to a CSV text file, all addresses must contain the same number of rows and each address set must be separated by the same number of rows. For example, change the address example to the following:    A1: Jane Clayton A2: Microsoft A3: 456 Elm Street A4: Sometown, USA 67890 A5: A6: A7: Jose Saraiva A8: 789 Oak Road A9: A10: Mytown, USA 54321 Note Each address set is now separated by two rows and each address set contains four rows.

To insert a new row in the Excel worksheet, select the Row heading where you want the new row. On the Insert menu, click Rows.

Note In Excel 2007, to insert a new row in a worksheet, select the row where you want the new row to be inserted, click Insert in the Cells group on the Home tab, and then click Insert Sheet Rows.  On the File menu, click Save As.

Note In Excel 2007, click the Microsoft Office Button, and then click Save As. In the Save As dialog box:  In the Save as type box, click CSV (Comma delimited) (*.csv). In the File name box, type a name for your CSV file (for example, Address.csv ), and then click Save.</li> Click OK when you receive the following message:

The selected file type does not support workbooks that contain multiple sheets.

• To save only the active sheet, click OK.

• To save all sheets, save them individually using a different file name for each, or select a file type that supports multiple sheets.

</li> Click Yes when you receive the following message:

Address.csv may contain features that are incompatible with CSV (comma delimited). Do you want to keep the workbook in this format?

• To keep this format, which leaves out any incompatible features, click Yes.

• To preserve the features, click No. Then save a copy in the latest Excel format.

• To see what might be lost, click Help.

</li></ol> </li> On the File menu, click Close, and then exit Microsoft Excel.

Note In Excel 2007, click the Microsoft Office Button, click Close, and then click Exit Excel.

Note You may be prompted to save the file again. When you are prompted, you can click Yes, repeat steps c and d, and then exit Excel.</li></ol>

Edit the CSV File in Microsoft Word
<ol> Start Microsoft Word.</li> On the File menu, click Open.

Note In Word 2007, click the Microsoft Office Button, and then click Open.</li> In the Files of type box, click All Files (*.*).</li> Click the CSV file that you saved in step 4 of the &quot;Edit the Excel Worksheet&quot; section, and then click Open.</li> On the Tools menu, click Options.

Note In Word 2007, skip this step.</li> On the View tab, click to select the All check box, and then click OK.

Note In Word 2007, 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 Paragraph marks under the Always show these formatting marks on the screen.</li></ol>

The Microsoft Word document is now similar to the following example:

<pre class="fixed_text">Jane·Clayton¶ Microsoft¶ 456·Elm·Street¶ &quot;Sometown, USA··67890&quot;¶ ¶ ¶ Jose·Saraiva¶ 789·Oak·Road¶ ¶ &quot;Mytown,·USA··54321&quot;¶

Note You may see a curved arrow instead of the symbol &quot;¶&quot;.</li> On the Edit menu, click Replace.

Note In Word 2007, click Editing in the Editing group on the Home tab, and then click Replace.</li> In the Find and Replace dialog box, click the Replace tab, and then follow these steps: <ol style="list-style-type: lower-alpha;"> In the Find what box, type ^p^p^p .</li> In the Replace with box, type %%%%% .</li> <li>Click Replace All.</li> <li>Click OK when you receive the following message:

Word has completed its search of the document and has made 1 replacement.

</li></ol>

Your Word document is now similar to the following example:

<pre class="fixed_text">Jane·Clayton¶ Microsoft¶ 456·Elm·Street¶ &quot;Sometown,·USA··67890&quot;%%%%%Jose·Saraiva¶ 789·Oak·Road¶ ¶ &quot;Mytown,·USA··54321&quot;¶

</li> <li>In the Find and Replace dialog box, do the following on the Replace tab: <ol style="list-style-type: lower-alpha;"> <li>In the Find what box, type ^p .</li> <li>In the Replace with box, type, .</li> <li>Click Replace All.</li> <li>Click OK to the following message:

Word has completed its search of the document and has made 7 replacements.

</li> <li>In the Find what box, type ,^p .</li> <li>Delete any text in the Replace with box so the box is blank.</li> <li>Click Replace All.</li> <li>Click OK to the following message:

Word has completed its search of the document and has made 1 replacement.

</li> <li>In the Find what box, type %%%%% .</li> <li>In the Replace with box, type ^p .</li> <li>Click Replace All.</li> <li>Click OK to the following message:

Word has completed its search of the document and has made 1 replacement.

</li></ol> </li> <li>Close the Find and Replace dialog box.

Your Word document now resembles the following example:

<pre class="fixed_text">Jane·Clayton,Microsoft,456·Elm·Street,&quot;Sometown,·USA··67890&quot;¶ Jose·Saraiva,789·Oak·Road,,&quot;Mytown,·USA··54321&quot;¶

</li> <li>On the File menu, click Save.

Note In Word 2007, click the Microsoft Office Button, and then click Save.</li></ol>

Additional query words: XL2007

Keywords: kbexpertisebeginner kbimport kbfindreplace kbsavefile kbconversion kbopenfile kbinfo KB819964

-

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

© Microsoft Corporation. All rights reserved.