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

Edit the Excel Worksheet

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 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.
3. On the File menu, click Save As.
   
   Note In Excel 2007, click the Microsoft Office Button, and then 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 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.
5. 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

1. Start Microsoft Word.
2. On the File menu, click Open.
   
   Note In Word 2007, click the Microsoft Office Button, and then click Open.
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 Worksheet" section, and then click Open.
5. On the Tools menu, click Options.
   
   Note In Word 2007, skip this step.
6. On the View tab, click to select the All check box, and then click OK.
   
   Note In Word 2007, follow these steps:
   1. Click the Microsoft Office Button, and then click Word Options.
   2. Click Display.
   3. Click Paragraph marks under the Always show these formatting marks on the screen.
   
   
   The Microsoft Word document is now similar to the following example:

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

   
   
   Note You may see a curved arrow instead of the symbol "¶".
7. On the Edit menu, click Replace.
   
   Note In Word 2007, click Editing in the Editing group on the Home tab, and then 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 is now 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. Close the Find and Replace dialog box.
    
    Your Word document now resembles 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.
    
    Note In Word 2007, click the Microsoft Office Button, and then click Save.

Back to the top | Give Feedback