Apologies if this is the wrong forum, wasn't sure where to head,


I have been given a text dump which runs into thousands of pages in a truly awful format. I had hoped to import this text file into an Access table (via Excel if necessary) which I could run queries against. The system it comes from is ancient and pretty much locked down, I can't get into thing to try to alter the format at it spits the data into the .txt file it creates. The vendor of the software went belly up a while ago so there is no support to call on.

I have copied and pasted a sample of below.

The only parts I am interested in is the text that appears after

Partner
Fee Earner
Department
Work Type

Having the date somehow extracted from the end of that long header that appears for each record would be very useful too, giving me 5 columns (using the first record in the list below) that would look like

__ Partner__________Fee Earner__________ Department_______________________ Work Type______________________ Date
ZMK director1______ BR another name ______ C&D Commercial & Development (Closed) ___MLA0 Misc Advice - L&CS _________ 23/11/04

and so on for the remainder of the file. (approx 25k records)

Can anyone assist me with a solution or suggestions which will extract only the data that appears after these respective headings and sticks it into a columnar format shown above so I can then run queries against it?

Here is a sample of the text file and how it looks within

Many thanks for your time and any suggestions you may have.

jack

------------------------------------------------------------------------------------------------------------------------------------
AXAC519 LC Services User: BXS New matter report Time 15:00 Date 11/06/09
V.0300 No selection made Matters opened from 11/07/00 to 11/06/09 VDU No. 01 Page 659
------------------------------------------------------------------------------------------------------------------------------------
Primary selection: ALL

Client/Matter No. Client name Matter description Date opened
----------------- ----------- ------------------ -----------
CMD 00001620 L&CS - Commercial & Development (Client Closed) use DL, MLA0 Chesters Gdns Repayment of Disco 23/11/04
Branch 0001 LC Services
Partner ZMK director1
Fee earner BR another name
Department C&D Commercial & Development (Closed)
Work type MLA0 Misc Advice - L&CS
Business source 0001 N/A
Business type 0001 N/A Estimated cost 0.00

CMD 00001621 L&CS - Commercial & Development (Client Closed) use DL, MLA0 Chapel View Repayment of Discoun 23/11/04
Branch 0001 LC Services
Partner ZMK director1
Fee earner BR another name
Department C&D Commercial & Development (Closed)
Work type MLA0 Misc Advice - L&CS
Business source 0001 N/A
Business type 0001 N/A Estimated cost 0.00

CMD 00001622 L&CS - Commercial & Development (Client Closed) use DL, MLA0 Lovett Walk Not of Transfer 25/11/04
Branch 0001 LC Services
Partner ZMK director1
Fee earner BR another name
Department C&D Commercial & Development (Closed)
Work type MLA0 Misc Advice - L&CS
Business source 0001 N/A
Business type 0001 N/A Estimated cost 0.00

CMD 00001623 L&CS - Commercial & Development (Client Closed) use DL, MLA0 - Lord Lawson Youth Wing Licence 25/11/04
Branch 0001 LC Services
Partner ZMK director1
Fee earner ZPCM person mentioned (Leaver)
Department C&D Commercial & Development (Closed)
Work type MLA0 Misc Advice - L&CS
Business source 0001 N/A
Business type 0001 N/A Estimated cost 0.00

CMD 00001624 L&CS - Commercial & Development (Client Closed) use DL, LRS Land adjoining Stannerford Road 25/11/04
Branch 0001 LC Services
Partner ZMK director1
Fee earner CJ another person
Department C&D Commercial & Development (Closed)
Work type LRS Land Registry Searches
Business source 0001 N/A
Business type 0001 N/A Estimated cost 0.00

CMD 00001625 L&CS - Commercial & Development (Client Closed) use DL, MGMT Service Plan 25/11/04
Branch 0001 LC Services
Partner ZMK director1
Fee earner MB another person
Department C&D Commercial & Development (Closed)
Work type MGMT Management
Business source 0001 N/A
Business type 0001 N/A Estimated cost 0.00

If there's always eight rows of data for each entry, as you have listed, you can do the following:

1. Use the attached FormatData.xls Excel file
2. Paste the data starting with the first "CMD" line into Excel, under the "Data" column header
3. Run the FilterBlanks macro
4. Select all rows that have blue row numbers, right-click, and choose Delete Row (make sure to select and delete the entire rows)
5. Remove the filter
6. Run the GetRecords macro
7. The data you need should now be in columns B through F

Regards,

Ax

Thank you sooooooo much, your solution worked like a charm.

I had considered delimiters and running find and replace and god knows what else, but watching your spreadsheet in action was a humbling experience to say the least.

Fantastic Ax.

Regards,

Jack

Great to hear Jack! You're welcome, I do a lot with data manipulation and formatting in Excel, you explained it well enough to make it pretty smooth.

Cheers,

Ax