How to convert a single column of addresses to a CSV text file in Excel (819964)

MORE INFORMATION

Edit the Excel Document

1. Open your Excel workbook that contains the address data.
2. 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 your 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 your Excel worksheet, select the Row heading where you want the new row. On the Insert menu, click Rows.
3. On the File menu, click Save As.
4. In the Save As dialog box:
   1. In the Save as type box, click CSV (Comma delimited) (*.csv).
   2. In the File name box, type a name for your CSV file (for example, Address.csv), and then click Save.
   3. 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.
   4. Click Yes when you receive the following message:Address.csv may contain features that are not compatible 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.
5. On the File menu, click Close, and then quit Microsoft Excel.
   
   Note You may be prompted to save the file again. If so, click Yes, repeat steps c and d, and then quit Excel.

Edit the CSV File in Microsoft Word

1. Start Microsoft Word.
2. On the File menu, click Open.
   
   The Open dialog box opens.
3. In the Files of type box, click All Files (*.*).
4. Click the CSV file that you saved in step 4 of the "Edit the Excel Document" section of this article (for example, Address.csv), and then click Open.
5. On the Tools menu, click Options.
6. On the View tab, click to select the All check box, and then click OK.
   
   Your Microsoft Word document now looks similar to the following example:

   Jane·Clayton¶
   Microsoft¶
   456·Elm·Street¶
   "Sometown, USA··67890"¶
   ¶
   ¶
   Jose·Saraiva¶
   789·Oak·Road¶
   ¶
   "Mytown,·USA··54321"¶
   

7. On the Edit menu, click Replace.
8. In the Find and Replace dialog box, click the Replace tab, and then follow these steps:
   1. In the Find what box, type ^p^p^p.
   2. In the Replace with box, type %%%%%.
   3. Click Replace All.
   4. Click OK when you receive the following message:Word has completed its search of the document and has made 1 replacement.
   Your Word document now looks similar to the following example:

   Jane·Clayton¶
   Microsoft¶
   456·Elm·Street¶
   "Sometown,·USA··67890"%%%%%Jose·Saraiva¶
   789·Oak·Road¶
   ¶
   "Mytown,·USA··54321"¶
   

9. In the Find and Replace dialog box, do the following on the Replace tab:
   1. In the Find what box, type ^p.
   2. In the Replace with box, type ,.
   3. Click Replace All.
   4. Click OK to the following message:Word has completed its search of the document and has made 7 replacements.
   5. In the Find what box, type ,^p.
   6. Delete any text in the Replace with box so the box is blank.
   7. Click Replace All.
   8. Click OK to the following message:Word has completed its search of the document and has made 1 replacement.
   9. In the Find what box, type %%%%%.
   10. In the Replace with box, type ^p.
   11. Click Replace All.
   12. Click OK to the following message:Word has completed its search of the document and has made 1 replacement.
10. Click Close to close the Find and Replace dialog box.
    
    Your Word document now looks similar to the following example:

    Jane·Clayton,Microsoft,456·Elm·Street,"Sometown,·USA··67890"¶
    Jose·Saraiva,789·Oak·Road,,"Mytown,·USA··54321"¶
    

11. On the File menu, click Save.