Looking to Clean Up Messy Dataset With No Clear Pattern

[rylock]

Looking for any tricks to clean up a ~100k record table that has no clear pattern. The data generally looks like this all blocked together in separate rows but the same column:

Please Login or Register  to view this content.

The big problem is that some records have three sets of additional data beneath it and some records have five -- maybe they have an email and/or phone number maybe they don't, some have lines after the record some don't, etc.

I'm looking for ideas either using code or formulas to attempt to clean this up to look like below without going through every line manually:

Please Login or Register  to view this content.

Hoping people might have ideas using scenarios they've had to use in the past on really messy datasets that come in like this; it could be some combination of using SEARCH() or LEN() to try and identify when each record's data is over -- or VBA or something.

I know it's not the most pointed question -- but if anybody has any tips it'd really help me out. It also doesn't have to end up being perfect -- if it ends up looking mildly like above, I'll be able to clean it manually from that point on, just not from the start.

Any help using any method would be greatly appreciated!

[José Augusto]

Hi
See the file I have attached.
Column C is a possible approach of the solution depends on the level of trust that you select in P2.
Regards

[samba_ravi]

it is better to attach sample excel file