View Single Post
 
Old 07-15-2015, 08:57 AM
dmarie123 dmarie123 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Dec 2012
Location: New Yuk
Posts: 23
dmarie123 is on a distinguished road
Default Extract Line of Text w/ specific characters up to the paragraph character, send to Excel

Hi all,

Long time no see . I swear I've seen similar questions but I can't find them now...

So, I receive the below data (I've "x"ed out the sensitive stuff) in a 380ish page report every day. There are about 1,000 instances of the text paragraph example. I need to extract the first line of each instance and send it to Excel so then I can import it into Access and build a query to search for businesses that have certain words in their names. However, getting the first line of text out of Word and into Excel so that I can then import it into Access is not as simple as I thought it would be. I found this, but it doesn't work at my end and I don't understand how to limit it by the paragraph character or get it to Excel.

Example:
150710000XXX CORP NAME, LLC
LAW : XXX LLC *FILER ADDRESS / PROCESS ADDRESS*
COUNTY : XXXX XXXXXXX NAME
EFF. DATE: 07/10/2015 123 XXXXXX ST. #XX
DUR. DATE: NEW YORK, NY XXXXX

I'd like to be able to extract the first line (bolded above) that holds that ID number, the corporation name and then ends at a paragraph return, and send those to Excel, two columns per record, one for the ID and one for the corporation name. The corp names don't always end in LLC so the paragraph return after the ID number should grab everything.

-The first six digits of the number beginning with 150710 above indicate the year, month and day. I'd like to leave the option open to enter either 4 or 6 digits, if we have to specify this now, because we will probably combine the reports for each week in to a single document.

-There is a paragraph character (^p) at the end of the line I want to extract because I was able to "find" them using Ctrl+F. There is not always a paragraph character preceding the 150710XXXXXXX number.

-Additionally there are three spaces between the ID Number and the corporation name.

Based on my limited knowledge of VBA I'd say a pop-up box that requests the prefix I want to search for would be best? Then, hypothetically, Word would use the entered prefix with a wild card to find all the instances that contain that prefix, extracting the line of text to the paragraph return. Ultimately those would all go into an Excel spreadsheet that I could then put in to Access so that I can build a query to search the keywords we're looking for as opposed to the poor soul that is currently Ctrl+Fing her way through almost 400 pages every day.

I'm using MS Office 2013. Going to update my profile right now.

Thank you in advance for your assistance.
Donna
Reply With Quote