VBA: Find start and end text in TXT file, copy all data in between to specified Excel Column

[jeffcoleky]

I'm trying to get data from multiple TXT files into Excel. Each TXT file represents 1 row of data on the excel workbook. Each section of text the TXT file represents the corresponding column in that row.

For example: Everything after "Other Observed Names" would go in a column labeled "Other Observed Names" in the excel file. Or in this case, the value would be "SHERRY L JONES (01/01/1988 to 06/01/2016)
SHERRY G JONES (01/01/1988)" The stopping point for each value is when there is a empty line in the TXT file. Multiple lines of txt are copied to single cell in Excel.

Not every TXT file is the same. For example, it might not have a "Professional Licenses" section so the TXT file would be shorter. I only need the following:

1. First Line
2. Dates of Birth
3. Dates at Searched Location:
4. Professional Licenses
5. Possible Relatives
6. Indicators
7. Possible Phones
8. Possible Emails
9. Address History

So can anyone help me write a macro that would:

• open each txt file
• search for each of the specified values
• copy everything after it through the empty line
• then paste it in the corresponding column

The macro should grab the data from every TXT file in C:\DATA and place it into a C:\OUTPUT\ExtractedData.xlsx file. Thanks for any help you can offer!

Here's some example raw Data:

FOR REAL ESTATE / FINANCIAL / BROKERAGE PURPOSES ONLY - 6006 MARKET ST ANN ARBOR MI 48191 JEFFERSON-People-Search-201905227751







SHERRY C JONES, 66 Years Old (ANN ARBOR, KY) 6006 MARKET ST, ANN ARBOR, MI 48191-1940 (JEFFERSON COUNTY) (04/1986 to 05/24/2019)


SHERRY C JONES (04/01/1980 to 04/07/2019)
SHERRY COX JONES (12/29/2014 to 08/20/2018)
SHERRY KAY JONES (12/05/2012)


Other Observed Names
SHERRY L JONES (01/01/1988 to 06/01/2016)
SHERRY G JONES (01/01/1988)


Dates of Birth
DOB: 09/1952
Age: 66


Other DOB: 09/1949
Age: 69


Dates at Searched Location: 04/1986 to 05/24/2019


Professional Licenses
SHERRY JONES
6006 MARKET ST, ANN ARBOR, MI 48191-1940 (JEFFERSON COUNTY)
NURSE
Kentucky 1041467
First Issued: 08/1974


Possible Relatives
Allen R JONES 01/1949 Age: 70
Brent JONES 05/1986 Age: 44
Tamara Jeanine Mcgill 01/1988 Age: 41


Indicators
Bankruptcies: None Found
Liens: 24 Found, Latest in 2018
Judgments: 1 Found, Latest in 1996
Utilities: 2 Found


Cities
ANN ARBOR, MI (04/1980 to 05/24/2019)


Possible Phones
(652) 448-1295 (CT) (Mobile) (90%)
(777) 821-9798 (ET) (Mobile) (86%)
(777) 241-5866 (ET) (LandLine) (78%)
(777) 442-4484 (ET) (Mobile) (66%)
(777) 562-4680 (ET) (LandLine) (66%)
(777) 241-4815 (ET) (LandLine) (66%)
(652) 724-6894 (CT) (LandLine) (42%)
(777) 614-5167 (ET) (VoIP) (15%)
(777) 290-7192 (ET) (VoIP) (4%)
(227) 886-8976 (CT) (LandLine) (4%)


Counties
Jefferson County, MI (04/1980 to 05/24/2019)


Possible Emails
slf9135@gmail.org
s9115@hotmail.org
saJONES@erols.org
sherrysJONES@hotmail.org
mmqdswr@google.org


Address History (5)
6006 MARKET AVE, ANN ARBOR, MI 48191-1940 (JEFFERSON COUNTY) (04/1986 to 05/24/2019)
Subdivision Name: FERNBROOK
7417 N JENEVIEVES ST, ANN ARBOR, MI 48128-1749 (JEFFERSON COUNTY) (06/01/2016 to 08/20/2018)
5512 GASKINS ST, ANN ARBOR, MI 48129-2957 (JEFFERSON COUNTY) (05/24/2016 to 05/24/2016)
8444 SHEPHERDSVILLE AVE, ANN ARBOR, MI 48119-4540 (JEFFERSON COUNTY) (04/1980 to 01/24/2004)
8444 SHEPHERDSVILLE AVE # OLD, ANN ARBOR, MI 48119-4540 (JEFFERSON COUNTY) (04/01/1980 to 04/01/1980)


05/27/2019

Here are 5 TXT files of raw data:
Sample file 1: https://www.dropbox.com/s/aa2mmt9864k2h3d/sourcefile1.txt?dl=0
Sample file 2: https://www.dropbox.com/s/l7y27gxk7r6m21n/SourceFile2.txt?dl=0
Sample file 3: https://www.dropbox.com/s/9po1rzpbct3oc4u/SourceFile3.txt?dl=0
Sample file 4: https://www.dropbox.com/s/cwm9w79fyd5e8ti/SourceFile4.txt?dl=0
Sample file 5: https://www.dropbox.com/s/sm0pzxfrdrk5xqh/SourceFile5.txt?dl=0

Here is an example ExtractedData.xlsx file on how it would look like: https://www.dropbox.com/s/7k40yca6fg72h8n/ExtractedData.xlsx?dl=0
Same file in Google Sheets: https://docs.google.com/spreadsheet...4xsSLh9tlkCl96qM1yLangTGKAY/edit#gid=11801720

PS. We also need anything after "FOR REAL ESTATE / FINANCIAL / BROKERAGE PURPOSES ONLY - " but before an empty row. EG: "6006 MARKET ST ANN ARBOR MI 48191 JEFFERSON-People-Search-201905227751"