Macro for .txt to help format for excel conversion

So this isn't technically an excel question I suppose but it relates enough to it that I thought I would ask here. I have a file which is similarly formatted to the attached document. It has a thousand+ lines all grouped in sets in a similar mannar, all starting with the Recipient ID and ending with "AD2,END;". I took most of the actual data out and put in the same number of characters of usless data because hte original had sensitive information.

I am trying to convert it into a meaningful Excel table to manipulate hte data from there but have run into a problem, some of the information for cells is seperated by a tab while others is seperated by a return. It happens in a predictible order which I think could be solved by a macro. That is what I am looking for help with.

It seems like the first return after the begning of each data set should only be a tab, following this the next return is ok, the third should be a tab and so on. Basically every other return should be a tab to make the document convert into an excel table that is usable.

So can any1 help me out, is there a way to right code to manipulate the text file to achieve this or am I stuck with a .txt file that does me no good?

thanks

sample.txt

Here is a code that will convert your txt file to excel and open it.

Please Login or Register  to view this content.

Use the record macro feature to capture your editing decisions, if you need more help let me know!