Opening TXT/PRN Problem

[jake1016]

Excel 2003 SP3

Windows XP Professional SP2

I'm having a problem that I'm not sure belongs directed to this group so let me know where to send it if necessary.

My problem is when I open a PRN or TXT file using Excel. In the past couple of months it reacts differently than it used to: Excel used to put all the data for one line in one cell (A1 then the next line would go into B1 etc.) and then I would run TextToColumns (via macro) and parse out the data and put in the headers. Now if I open the file with Excel it will only put the data up to the first space into cell A1 and then the next piece of data up to a space in B1 etc. I'm thinking there must be some setting that I've changed or has changed as a result of automatic updates but I can't figure out what it might be.

Here is what the data looks like (this is 2 rows of several hundred):

"UHJ0000003333","05",20080103,9999,20080103,"986B","265118","C M MXXX "," ","186 ", .00, 9.1500, .00, .00,20080522, .00, .00, .00, .00, .00, .00, .00, .00, .00, .00, .00, .00, .00, 8.00, .00, 8.00, 8.00,40, .00, .0000, .00,"B"," "," ","Y","F"
"UHJ0000011111","07",20071220,9999,20071220,"41RE","638400","R N SYYYYYYYY "," ","061 ", .00, 8.7500, 1270.95, .00,20080522, .00, .00, .00, 4.25, .00, .00, .00, 4.25, .00, .00, .00, .00, .00, .00, .00, .00, 4.25,40, .00, .0000, .00,"B"," "," ","Y","F"

In my environment if I copy the data above, paste it into NotePad, save that file as MyTest1.prn (or .TXT it doesn't make any difference) and then do an OpenWith Excel on that file the data up to the first space right after the C goes into A1 the M goes into B1 the MXXX goes into C1 etc. What used to happen and what I want to happen is that I want all of one line's data from "UHJ... to just before the next "UHJ to go into cell A1.

Any ideas on what might be happening and how to fix it would be appreciated.

 

[SkipVought]

Hi,

You have a parsing value stuck in the Text to Columns wizard.

Select ONE cell containing junk data.

Data > Text to columns...

I'll bet that the Delimiter option button is selected. Select Delimited and NEXT.

UNCHECK all boxes. FINISH

Now open your file.

HOWEVER, I would recommend, instead of OPENING a .txt or .prn or .csv file to IMPORT. You will have much more control over the results.


Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[jake1016]

Skip, you are correct about the settings but unchecking the boxes makes no difference. I do use the IMPORT sometimes but I've got a number of macros that use TextToColumns assuming that all of the data is in one column. I'm just lazy about recreating them to use IMPORT. I guess that's what I wind up doing though. Thanks.

 

[SkipVought]

I don't know???

"...M goes into B1 the MXXX goes into C1..." is EXACTLY what would happen if SPACE were selected in Text to columns.

You might ALSO change the OPTION to FIXED WIDTH, and again verify that SPACE is not selected.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]