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.

REFERENCES

For more information about the TextWizard, choose the Search button in Help and type:

   text in columns

KBCategory: kbother KBSubcategory: Additional reference words: 5.00 5.00c 7.00 Keywords : kbother Version : 5.00 5.00c 7.00 7.00a | 5.00 Platform : MACINTOSH WINDOWS THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Last reviewed: March 27, 1997 ©1997 Microsoft Corporation. All rights reserved. Legal Notices.