pulling data from a text file via MACROs

[old buckeye]

I need a macro that will go through every .txt file in a folder and when it finds a string of data, it pulls the next 9 characters and puts it in my spreadsheet.

 

[jimrward]

is your data file structured in columns or random data layout, and how big is your file

can you post a small sample of the data, with confidential info substituted out

 

[old buckeye]

basically, it is random. In fact, there are at least two different file types that I am concerned with. I cannot post attachments, but one of the files looks like this:


Burger Data Processing Status Report
Report Run Date 03/21/2013 09:44PM
Bob's Burger Contract ID: H9999
PROD

Page 1 Submission Interchange Number: ENC002500001326320130317
Report Date: 03/21/2013
Report ID: BBB-002 Transaction Date: 03/19/2013

Encounter
Line Encounter

another example:

0*BBB-002*20130323*20130322*Burger Data Processing Status Report* *ENC002500001328420130318 *INS*PROD*

 

[jimrward]

and which data are you looking for and what are you hoping to return, using the sample data you have posted.

the data you are trying to return, is that data line always in the same sort of format, for example
Page 1 Submission Interchange Number: ENC002500001326320130317

we could read the line in and look for "Interchange Number" and extract the bit after the colon

the second example of your data would be easier to process, we read in the line, split into an array and compare array elements

 

[old buckeye]

I am working with several different file type with different formats. They do not all contain the phrase "Interchange Number". The only constant in any of the various files is ENC0025. The part that I want to extract is the next 9 characters after the ENC0025. That would be 000013263 in the example.