XL: Text to Columns May Yield Incorrect Results

ID: Q115074

The information in this article applies to:

• 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, 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.

   your data, and then click Next.

   Text option under the Column Data Format (do this for each column).

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.

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

Keywords          : kbdta xlwiz 
Version           : WINDOWS:97,7.0,5.0,5.0c
Platform          : WINDOWS

Last Reviewed: January 8, 1999