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

Article translations Article translations
Article ID: 819964 - View products that this article applies to.
Expand all | Collapse all

On This Page

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

  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.

Properties

Article ID: 819964 - Last Review: September 19, 2011 - Revision: 4.0
APPLIES TO
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Office Excel 2007
Keywords: 
kbexpertisebeginner kbimport kbfindreplace kbsavefile kbconversion kbopenfile kbinfo KB819964

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com