XL: Text to Columns May Yield Incorrect Results

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows

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 desired formatting, do the following:

1. Select the range of data to parse.

2. From the Data menu, choose Text To Columns.

3. In step 1 of the Text Import Wizard, choose either Delimited or Fixed Width (depending on the layout of your data), and then choose Next.

4. In step 2 of the Text Import Wizard, select the proper Delimiter for your data, and then choose Next.

5. In step 3 of the Text Import Wizard, select each column and choose the Text option under the Column Data Format (do this for each column).

6. Choose Finish.

7. To save your file as a Microsoft Excel file, choose Save As from the File menu, and, under File Type, select the Microsoft Excel Workbook.

MORE INFORMATION

A Microsoft Application Note is also available on Text importing. For information about what this Application Note discusses and how you can obtain it, see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q102142
   TITLE     : "Excel AppNOTE: Opening and Saving Text Files (ME0802)"

   ARTICLE-ID: Q99844
   TITLE     : "Excel AppNOTE: Opening and Saving Text Files (WE0801)"