Article ID: 214028
Article Last Modified on 9/27/2003
APPLIES TO
- Microsoft Excel 2000 Standard Edition
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:
- Select the range of data to parse.
- From the Data menu, click Text to Columns.
- 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.
- In step 2 of the Convert Text to Columns Wizard dialog box, select the proper delimiter for your data, and then click Next.
- 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).
- Click Finish.
- 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).
Additional query words: XL2000
Keywords: kbbug kbpending KB214028
