Excel Formatting & Capabilities Question

Hi jindon,

Originally Posted by jindon

Here's my interpretation of your problem.
........

_ OK. The OP’s start point is a very messy list of rows ( stored somewhere. He was not too specific where, but it is probably a text file or similar. May even be an excel file. But that is not important. ) - He gave a typical set of 7 messy rows in Post # 1.
He wants those to be sorted into a neat list of tidy rows. The final list could be a Comer delimited file. But that is also not important.

_ The main point of the excessive to get those messy rows sorted into neat ordered tidy rows. Me and jmhuk suggested putting the initial messy rows all in column A , then attempting to place the ordered sections for each row in other columns to the right. Once that was done it would be straight forward to join all those columns back into ordered rows, with the sections separated by Comers or wotever ( whatever ).. The reason why me and jmhuk suggested pasting out to columns initially was so that the OP could then check through and make any necessary corrections manually before the columns would then be joined back to produce ordered rows. We suggested this as we thought it was barely possible to take such a messy list of rows such as the OP had and fully by VBA code get it perfect. I expect if such a thing is humanly possible, it may take someone of your abilities to achieve that. But we expect the nature of the OP’s messiness is such that it is just not possible

_ Along the way, the OP also produced some simple ordered rows, ( not quite representing the actual data headings he wants, which he also gave in Post # 1 ) . This was done, i think, just to demonstrate a nice “clean” ordered list of rows, as a comparison with his messy list of rows.

_ I do not quite follow all your code workings, but you appear to be importing this set of ordered rows into am excel File.
_ Apologise if i am wrong, But I think what you have done has no relevance to the OP’s requirement. But I may have missed something

Alan

P.s. I am particularly intrigued as to what this part in your code does:
.Pattern = "\b\S{1,3} "
I have only just started looking at Regex stuff and i cannot figure out that line.
_ could you explain that bit please.
I think i almost follow most of the other workings in your code.
Thanks