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

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, please click the article number below to view the article in the Microsoft Knowledge Base:

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

Additional query words: text import wizard XL97

Keywords : kbdta xlwiz
Version : WINDOWS:5.0,5.0c,7.0,97; winnt:5.0
Platform : WINDOWS winnt
Issue type :