TXT File - Text to Columns Data Dispersed

[cft]

I deal with very large .txt files that, when imported into Excel, have to be "conditioned" via macro to "clean" the data. PROBLEM: The data (rows) is sporadic (sample following):
3/21/2008 10:26 TSC Refill E NAME 7142125 Order: 37207524
3/21/2008 12:14 AGH E NAME 7536895 Order: 37210344
3/21/2008 12:34 JVM Refill E NAME 7736335 Order: 37212980
3/22/2008 8:27 CYM Refill E NAME G 7089200 Order: 37211803
3/24/2008 10:19 EPO How To Use E NAME E 5202260 Order: 31769204
3/24/2008 13:17 AGH Order Inquiry E NAME L 7263859 Order: 37263192
3/24/2008 9:20 JVM Refill E NAME B 7115886 Order: 37249504
3/25/2008 8:41 CEC Injectable E NAME L 6118052
3/25/2008 11:21 E NAME 7093079 Order: 37294179
3/25/2008 10:52 E NAME M 6866437 Order: 37292740
3/26/2008 8:43 JVM Refill E NAME 7347112 Order: 37319189
3/27/2008 5:38 KBS Order Inquiry E NAME F 6099773 Order: 36902088
3/27/2008 5:35 CYM Order Inquiry E NAME W 7217880 Order: 37242902
3/28/2008 16:48 CHA Injectable E NAME L 6750719 Order: 36329018
3/28/2008 9:57 HJG Refill E NAME L 5279630 Order: 37389439

The data is actually complete in each single row, and the data elements are seqeuntial, but grouped incorrectly. I need to be able to create a macro / vba that "reads" the contents of each cell in each column and determines if the content belongs (such as the date and time columns, followed by 3 initials followed by ...etc...). I need to create a procedure that states that IF in column C - there are fewer than 3 letters (user initials) then move the contents of the cell to the right 4 columns in the same row, and if there are more than 3 letters, move the contents of the row to the right 3 columns in the same row, etc.
QUESTION: How do I make the sheet read down each column, deterimine what is in the cell and then move it to the correct column so the data is grouped in a unified manner? Any suggestions will be greatly appreciated.

 

[Brianwarnock]

A week and no response, but then when I read the post I could not fathom what was required.Take these entries, what should the resulting sheet look like. No etc please

3/21/2008 12:34 JVM Refill E NAME 7736335 Order: 37212980
3/22/2008 8:27 CYM Refill E NAME G 7089200 Order: 37211803
3/24/2008 10:19 EPO How To Use E NAME E 5202260 Order: 31769204
3/24/2008 13:17 AGH Order Inquiry E NAME L 7263859 Order: 37263192
3/25/2008 11:21 E NAME 7093079 Order: 37294179

Brian

 

[chergh]

I know what the poster is asking but it would just be a nightmare to try and code it, if it really would be possible at all. He pretty much needs to build an array of the whole text document using the split function and then parse each element in the resulting array to determine if it belongs in a new column or in the current column, and its that decision if it belongs in a new or current column that is going to be a nightmare.

What the poster really needs to do is go to whoever is suppling the text file and tell them to use XML.