Unable to do data parsing into readable and useful format in Office 2010

[theo28]

Hi,

I have data in the format as attached in original data.txt. I want to convert it into the format as mentioned in Final desired output.xls,But Im unable to do this .Can someone help me out with this. I have like 50000-70000 lines of data similar to original data.txt.

Thanks,
Regards,
theo28

[azumi]

Assumed your data in A2 and down:

=TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE($A$2,"*"," "))," ",REPT(" ",LEN($A$2))),(COLUMNS($A$2:A2)-1)*LEN($A$2)+1,LEN($A$2)))

copied cross until shows blank cells

Azumi

[theo28]

Assumed your data in A2 and down:

=TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE($A$2,"*"," "))," ",REPT(" ",LEN($A$2))),(COLUMNS($A$2:A2)-1)*LEN($A$2)+1,LEN($A$2)))

copied cross until shows blank cells

Azumi

Hi Azumi and Excel users,

Thanks for the help but it does not give the output desired by me. I was able to make it little legible by using "Text to columns and separate by * ".This is represented in 'original sheet' of original text parsed but incomplete.xls The final output has to be in a single row as shown in the 'final sheet'. Could you or anyone else help me attain this desired output?

Thanks.
Regards,
theo28

[azumi]

Fix it...

=TRIM(IF(COUNTA('original sheet'!A3:A7)=1,'original sheet'!A3,IF(COUNTA('original sheet'!A3:A7)=2,TRIM('original sheet'!A3)&TRIM('original sheet'!A4),IF(COUNTA('original sheet'!A3:A7)=3,TRIM('original sheet'!A3)&TRIM('original sheet'!A4)&TRIM('original sheet'!A5),IF(COUNTA('original sheet'!A3:A7)=4,TRIM('original sheet'!A3)&TRIM('original sheet'!A4)&TRIM('original sheet'!A5)&TRIM('original sheet'!A6),IF(COUNTA('original sheet'!A3:A7)=5,TRIM('original sheet'!A3)&TRIM('original sheet'!A4)&TRIM('original sheet'!A5)&TRIM('original sheet'!A6)&TRIM('original sheet'!A7)))))))

Hope it works

[azumi]

Repost the file

[azumi]

Or this without condition:

=CONCATENATE(TRIM(A3),TRIM(A4),TRIM(A5),TRIM(A6),TRIM(A7))

Azumi

[theo28]

Hi Azumi
Thanks for your response.

I have 50000-70000 lines of data which I want to process. I want to automate it or if one formula can be dragged to update multiple rows,it would be very useful.
Also there is difference in number of lines between successive entries as well as number of rows that actually have data.
Im attaching a sample of 200 rows which is around 1% of the data that needs to be processed in the above manner.

Regards,
theo28