Microsoft KB Archive/125182

= Microsoft Knowledge Base =

XL5: Column Data Format in Text To Columns Wizard Ignored
Last reviewed: March 27, 1997

Article ID: Q125182

5.00 5.00c 7.00 7.00a | 5.00

WINDOWS              | MACINTOSH kbother

The information in this article applies to:


 * Microsoft Excel for Windows, versions 5.0, 5.0c
 * Microsoft Excel for Windows 95, versions 7.0, 7.0a
 * Microsoft Excel for the Macintosh, version 5.0

SYMPTOMS
In Microsoft Excel, when you use the Text To Columns Wizard to parse data on your worksheet, the Column Data Format that you select in Step 3 of the wizard may not be applied the data as expected.

CAUSE
This behavior occurs because the Text To Columns Wizard does not apply formatting a cell that already contains formatting. For example, if you use the Text To Columns Wizard to parse the text "Date,1/1/94" using the comma as the delimiter, and you select the Date Column Data Format option for the second column, the destination cell for the date value is only formatted with the requested date format if the cell did not contain an existing format. If the destination cell for the data value contains a number format other than General, this format is used when the information is placed in the destination cell.

Note that if the destination cell contains data, you receive the following message:

Replace contents of destination cells? If you choose Yes, the contents are replaced, but the number formatting is not replaced unless it is formatted with the General format.

WORKAROUNDS
To apply the number formatting that you select in the Convert Text To Columns Wizard, do either of the following:

Method 1: Clear the number formatting in the destination cells before you

use the Text Wizard. You can do this with the following steps:

a. Select the cells that contain the formatting that you want to clear.

b. From the Edit menu, choose Clear, and then choose Formats. Method 2: After you use the Text Wizard to parse your data, use the Format

Cells dialog box to apply the desired number formatting to the cells that contain your data.

STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.