pattern recognizing in excel for a large data set

hello all i have a 34 MB file that i will be bringing in from a .txt file and have data that is all lumped together. i want to set it up so that the first 9 numbers and letters are thrown into one column, the next 3 numbers are thrown into another column and the next 8 numbers are thrown into another column and so on and so forth. i have about 10-15 more columns in this fashion any advice ?

1TRANS20130925 193SPD DOMESTIC PORTFOL0001 102260193000000 0000193429DTCCCDT 20130925 21:29 RDT 20130925 21:32
01WREI 18383M47200220130926000001080005000000000000000238+0000000000000+0000183506612+0000000003670+0000000027493+0000004000000000000000000+
02AKR 0042391090002013092600000221WREI 18383M472002


Thanks for the input Tony but that wouldnt work so here is how the data pops up, so here is how i want the data to be separated in excel ..

02 AKR 004239109 000 20130926 00000221 WREI 18383M472002

Hi Guys thank you for the input. I am attaching a the raw data file here. As you would notice this is how the data appears in the txt file.

here is how i want to separate the data in excel.

- in A1 i want WREI
- starting in B2 i was looking to have the data come up as

AKR 004239109 000 20130926 00000221 WREI 18383M472002

About 110 lines down WREI changes to SOXL and then about 35 lines down it changes to AGLS so essentially my goal is to separate everything under each contraparty symbol ( WREI, SOXL, AGLS etc etc)

thank you all for all your help.

Raw Data File-1.txt

hi jaslake,

thanks for the code, my question is when i paste this data into excel there are 200,000 lines of data, will this code run as a loop through all the contraparty symbols ?

Thanks again for all your help guys

ok nevermind i just read your directions and i am not even pasting the text from the raw data file to the excel file.

oh sry when i mean contraparty symbols i am talking about the (WREI, SOXL, AGLS) that are present in the left hand side of the text file.

Do you want to list those "symbols"?

Please Login or Register  to view this content.

thanks for the code jindon, what do you mean when u say list those codes ?

Have you tried the code?

jaslake ur first code worked great, jindon i am having a error msg with i try to run your code

jaslake is there way to IM or chat here ?

yea how do i do that ?

no its fine, the problem has been solved. i would like to close this chat how do i do so

What I meant was this.

Hi jaslake thanks for your help with the code, i wanted to modify the code such that the data being parsed into one of the columns comes in as 2 different columns, and example is

old output
column h - 18383M472002

new output.
column h - 18383M472
column i - 002

how could i make this change.

thanks

jaslake, i understand those are the two lines that i would have to change but what do i put in the bottom line, i am assumming i would add a 2 to the top line, but how about the bottom line ?

hi jaslake it was is the logic behind adding the 18, how do u get that number. i am just trying to logically understand this code. i mean the code works great, but trying to understand just in case i have further changes that come along thanks.