Problem exporting multi-line contact street addresses in Outlook (198283)


The information in this article applies to:

  • Microsoft Office Outlook 2003
  • Microsoft Outlook 2002
  • Microsoft Outlook 2000
This article was previously published under Q198283

SYMPTOMS

When you export Microsoft Outlook Contacts in either a comma or tab separated values format, any street addresses that contain more than one line are exported with carriage returns separating the multiple lines. This may result in a skewed output file containing multiple records for those contacts containing multi-line street addresses.

CAUSE

In a comma or tab delimited file, carriage returns are interpreted as record delimiters.

RESOLUTION

There are two options for correcting this problem:

Use CSV Format

Export the file in comma separated values (CSV) format; then open the file in Microsoft Excel. The file will import correctly with one record per contact and the multi-line street address in one cell. You may need to change the row height in order to see the second line of the address.

Note This option will not work if you export the file in the tab separated values (TSV) format.

Manipulate the file in Microsoft Word

Export the file in either a comma separated values or tab separated values format, and then open the output file in Word. Then, manipulate the data as follows:
  1. On the Edit menu, click Replace. If the Special button does not appear, click More to expand the dialog box.
  2. In the Find What box, type a double quotation mark ("). Then click Special and click Paragraph Mark.
  3. In the Replace With box, type any special character not found in your data, such as a pound sign (#).
  4. Click Replace All.
  5. In the Find What box, click Special and click Paragraph Mark.
  6. In the Replace With box, type whatever character you would like to use to separate the multiple street address lines, such as a percent sign (%).
  7. Click Replace All.
  8. In the Find What box, type the special character used in step 3.
  9. In the Replace With box, click Special and click Paragraph Mark.
  10. Click Replace All.
After completing this procedure, your file will have one record for each contact. The multi-line street address will be in one field, surrounded by quotation marks. Each street address line will be separated with the character chosen in step 6.

MORE INFORMATION

The following illustrates the problem described in the "Symptoms" section and the result of performing the procedure described in the "Resolution" section.

The example below has a 2-line street address: 

   John Doe
   1234 Mockingbird Lane (address line 1)
   Apt 589               (address line 2)
   Dallas, TX 75123


The exported file will contain a carriage return ([CR]) following the word "Lane" which results in two separate records for this one contact, as follows: 

   "John Doe","1234 Mockingbird Lane[CR]  (record 1)

    Apt 589","Dallas, TX 75123"[CR]       (record 2)


After performing the procedure described in the "Resolution" section, the resulting output will look as follows:

  • Using Excel: One record with a soft carriage return (SR) between Lane and Apt. As a result the entire address appears on two lines within a single cell.

    "John Doe","1234 Mockingbird Lane[SR]Apt 589","Dallas, TX 75123"[CR]
  • Using Word: One record with a percent sign between the first and second lines of the address.

    "John Doe","1234 Mockingbird Lane%Apt 589","Dallas, TX 75123"[CR]
Note This example assumes the percent sign "%" was used as the separator for the street address lines in step 6.

Modification Type:MajorLast Reviewed:7/13/2006
Keywords:kbprb KB198283
©2004 Microsoft Corporation. All rights reserved.