OL97: Postal Codes Import Incorrectly from Excel Workbook

• Microsoft Outlook 97
• Microsoft Excel 97 for Windows

SYMPTOMS

When you use the Microsoft Outlook 97 Import and Export Wizard to import a Microsoft Excel 97 file that contains postal codes, the postal codes import either with three decimal places or not at all.

CAUSE

You did not format all of the Excel cells as "Zip Code + 4." By default, Excel uses the General format for numbers. This format does not import properly into Outlook when you use the Excel converter in the Outlook Import and Export Wizard. If you use a mix of "Zip Code" and "Zip Code + 4" cell formats in the same worksheet, the numbers may not import properly. If you use five-plus-four digit postal codes formatted as "Zip Code", they may not import properly.

RESOLUTION

You can work around this problem two ways.

Method 1

The preferred method is to format the cells in Excel as "Zip Code + 4" whenever you use any type of postal code. Five-digit postal codes then import as 12345-0000 and five-plus-four-digit postal codes import as 12345- 1234. If you format the cells as "Zip Code," the imported results may contain decimals.

Follow these steps to set the cell format:

1. In Excel, select the cells containing postal codes.

2. On the Format menu, click Cells.

3. In the Category list, click Special.

4. In the Type list, click "Zip Code + 4" and then click OK.

Method 2

The second method is to export the file from Excel to a comma or tab delimited text file. Then import the delimited text file into Outlook.

MORE INFORMATION

When you create an Excel workbook sheet that contains postal code numbers in the five-digit format (12345) or in the five-plus-four-digit format (12345-1234), the postal code numbers do not import properly into Outlook. By default, the cell format is General. With this format, the five-digit numbers import with three decimal places. For example, the postal code 123345 imports into Outlook as 12345.000. The five-plus-four-digit numbers do not import at all. For example, Outlook imports the postal code of 12345- 1234 as a blank field.

If you format the cells as "Zip Code + 4," the data imports properly into Outlook.

Steps to Reproduce Problem

Follow these steps to create the Excel workbook:

1. In the first sheet of a new Excel workbook, create the following cells:

         A           B            C
      +--------+-------------+---------------+
    1 |FName   |     LName   |    ZipCode    |
      +--------+-------------+---------------+
    2 |John    |     Doe     |    12345      |
      +--------+-------------+---------------+
    3 |Jane    |     Doe     |    12345-1234 |
      +--------+-------------+---------------+
   
   

2. With your mouse, drag to select the above cells (A1:C3).

3. On the Insert menu, click Name and then click Define.

4. In the "Names in workbook" box, type MyRange.

5. On the File menu, click Save As and save your workbook.

6. Quit Excel.

1. On the Outlook File menu, click "Import and Export."

2. In the "Choose an action to perform" list, click "Import from Schedule+ or another program or file" and click Next.

3. In the "Select file type to import from" list, click "Microsoft Excel" and click Next.

4. In the "File to import" box, type the path and file name of your workbook, or click browse and navigate to your workbook. Click Next.

5. In the "Select destination folder" list, click your Contacts folder and then click Next.

6. In the "Following actions will be performed" list, click to check "Import <file name> into the 'Contacts' folder." This should open the "Map Custom Fields" dialog box. If it does not, click "Map Custom Fields" to open the Map Custom Fields dialog box.