Parsing data with wizard, interpretes as date (XP)

[Cecil]

We are downloading data from the mainframe and opening in Excel. The Text to column wizard is utilized, data is fixed length. The heading row contains data like

42-3 45-2 3-1

The first two work fine. However 3-1 is interpreted as a date.

Can I avoid this? The rows below contain numeric values, so loading columns as text is not an option.

Thanks.

[Andrew Cronnolly]

If you are opening a text file in Excel, the Text Import Wizard is launched to guide you through the process. With this wizard You first choice is to select Delimited or Fixed width columns. Clicking Next then allows you to either select the Delimiter(s) or create the fixed width columns. Clicking Next then allows you to select how each column should be interpreted as it is imported. This also applies when using the Text to Columns wizard. Step 3 allows you select each column and specify how it should be formatted, General, Text, Date etc.

See attached graphic.

Andrew C

[HansV]

In addition to Andrew's reply: you'll need to run the Text to Columns wizard twice:
- For the header row: set all columns to text using the method described by Andrew
- For the data rows: either let Excel decide the type, or set it to generel, text or date as needed.

[Cecil]

Thanks.

Telling Excel that the whole column is text is the only solution I could find too. A two step parsing exersize will not work either, since this file is in sections, with a header row, approx 10 data rows, another header row, approx 10 data rows, etc.

One other solution I thought of is, import all as text then copy the value of 1, do a "paste-special" - "Multiply". However, the results are the same, 3-1 converts to a date while 13-1 does not.

This means I will have to pound code, which I do not have time to do at the moment.

Oh well, code it is!

[sdckapr]

You could also import the entire thing as one "big" text column. Then delete the "header row" sections and then use text to columns.

You could possibly even have a macro do it by reading in the text file line by line and parsing in VB. If the the files have some "pattern" it could be coded relatively easily and then have this "spit out" the parsed data.

Steve