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

jeffcoleky

Active Member
Joined
May 24, 2011
Messages
274
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:
Code:
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"
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top