XL2000: Changing Text to Columns May Yield Incorrect Results (214028)


The information in this article applies to:

  • Microsoft Excel 2000
This article was previously published under Q214028

SYMPTOMS

In Microsoft Excel 2000, changing text to columns may yield incorrect results.

WORKAROUND

To work around this problem, you need to parse the text strings and maintain the intended formatting. To do so, follow these steps:
  1. Select the range of data to parse.
  2. From the Data menu, click Text to Columns.
  3. In step 1 of the Convert Text to Columns Wizard dialog box, select either Delimited or Fixed width (depending on the layout of your data), and then click Next.
  4. In step 2 of the Convert Text to Columns Wizard dialog box, select the proper delimiter for your data, and then click Next.
  5. In step 3 of the Convert Text to Columns Wizard dialog box, select each column, and then click the Text option under Column data format (do this for each column).
  6. Click Finish.
  7. To save your file as an Excel file, click Save As from the File menu, and then (under File Type) select Microsoft Excel Workbook.

STATUS

Microsoft has confirmed that this is a problem in Excel 2000.

MORE INFORMATION

A Microsoft Application Note is also available on text importing. For additional information about what this Application Note discusses and how you can obtain it, click the article number below to view the article in the Microsoft Knowledge Base:

102142 Excel AppNote: Opening and Saving Text Files (ME0802)

In Microsoft Excel, the Text to Columns command on the Data menu makes it easier to parse text from one column to the adjacent columns; this command also allows you to control the column delimiter.

However, when you use this command, the results may not be what you expect.

For example, leading zeros (0) may be dropped, and numbers that should be formatted as text are instead formatted as dates or scientific numbers (even when the source and destination columns have been formatted with the Text format).
Modification Type:MajorLast Reviewed:9/27/2003
Keywords:kbbug kbpending KB214028
©2003 Microsoft Corporation. All rights reserved.