Extract Address information from large text file

[cmical]

I've really been struggling with this one. I am not well versed in VBA as a forewarning.

I have a large text file containing data that I've scanned in from paper and have used OCR to create said text file. This file contains records of customer information. I need to extract only the address lines of the records. For example, the record contains 12-15 lines of information above the address lines, and another 12-15 lines before the next record starts. Each line that I want to extract (3) contain the following: 'Name(s):' 'Address:' and 'City/State/Zip:' before the actual data that I want to get at.

I'd like to import the text into column A, and then "process" that data into three separate columns: "Name" and "Address" and "City/State/Zip". I cannot figure out how to do this at all.

Any suggestions?

 

[hayden]

You could use power query for this.
Load the csv file into power query.
Promote headers
Filter lines - Filter-> Text Filter, Begins with (use Name:, Address: and City/State/Zip: ) - This will leave you with just the lines you care about.
Split column by delimiter using ":" - You'll have two columns of data, one that is the variable name (i.e. name, address etc) and one that is the value.
Pivot the table.

 

[cmical]

There is no CSV file for the data. It's a large text file that I plan on copy/pasting into a column in Excel.

With Power Query still work?

 

[hayden]

Yes, you can load the file into power query directly, or you can paste the data into excel, then load it into power query as a table. The first one is more efficent and will let you pull in files larger than 1.4 million rows which is the max you can have in excel. I added some youtube videos to help you out..

Load the text file into power query.
Promote headers
Filter lines - Filter-> Text Filter, Begins with (use Name:, Address: and City/State/Zip: ) - This will leave you with just the lines you care about.
Split column by delimiter using ":" - You'll have two columns of data, one that is the variable name (i.e. name, address etc) and one that is the value.
Pivot the table - I'll let you google this one.

 

[cmical]

Yes, you can load the file into power query directly, or you can paste the data into excel, then load it into power query as a table. The first one is more efficent and will let you pull in files larger than 1.4 million rows which is the max you can have in excel. I added some youtube videos to help you out..

Load the text file into power query.
Promote headers
Filter lines - Filter-> Text Filter, Begins with (use Name:, Address: and City/State/Zip: ) - This will leave you with just the lines you care about.
Split column by delimiter using ":" - You'll have two columns of data, one that is the variable name (i.e. name, address etc) and one that is the value.
Pivot the table - I'll let you google this one.

Hayden - you rock. I'm a visual dude and the videos will help me through this. I'll update this post when successful. Thank you.

 

[hayden]

You may need to add a step before pivoting the table. Something like a record number column. It would include three rows of 1, then three rows of 2, then 3 rows of 3 etc. I don't use pivot much, and I can't remember if you need that or not. You can use the add column from example, and I bet it would create that for you easily.