XL: Changing Text to Columns May Yield Incorrect Results (115074)


The information in this article applies to:

  • Microsoft Excel 97 for Windows
This article was previously published under Q115074

SUMMARY

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 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).

WORKAROUND

To parse the text strings and maintain the formatting you want, follow these steps:
  1. Select the range of data to parse.
  2. On the Data menu, click Text To Columns.
  3. In step 1 of the Text Import Wizard, click either Delimited or Fixed Width (depending on the layout of your data), and then click Next.
  4. In step 2 of the Text Import Wizard, select the proper Delimiter for your data, and then click Next.
  5. In step 3 of the Text Import Wizard, select each column and click the Text option under the Column Data Format (do this for each column).
  6. Click Finish.
  7. To save your file as a Microsoft Excel file, click Save As on the File menu, and under File Type, select the Microsoft Excel Workbook.
Modification Type:MinorLast Reviewed:8/15/2003
Keywords:KB115074
©2003 Microsoft Corporation. All rights reserved.