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
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.
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.
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.
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.
Edit the CSV File in Microsoft Word
Start Microsoft Word.
On the File menu, click Open.
Note In Word 2007, click the Microsoft Office Button, and then click Open.
In the Files of type box, click All Files (*.*).
Click the CSV file that you saved in step 4 of the "Edit the Excel Worksheet" section, and then click Open.
On the Tools menu, click Options.
Note In Word 2007, skip this step.
On the View tab, click to select the All check box, and then click OK.
Note In Word 2007, follow these steps:
Click the Microsoft Office Button, and then click Word Options.
Click Paragraph marks under the Always show these formatting marks on the screen.
The Microsoft Word document is now similar to the following example: