Word to Excel : Precise Text Extraction (automatic)
Business Requirement
This was very interesting. We had a large Word document with over 200 ‘listings’ of items, all laid out in similar ways. None were in tables. I’ve recreated the similar format, but totally modified the actual data to protect the confidentially of the original. It shows 2 listings. (Click on image to enlarge)
I had to extract out only the following elements for our purposes:
- Name (“Williamstown”, i.e. the heading)
- Code:
- Keywords:
- Location:
…and get them into an Excel spreadsheet. Let me state again, there were over 200 of such listings.
More than one person said to just sit down and copy’n’paste from one to the other. I knew there had to be a smarter, quicker and more automated way…
Results
After thinking about it and trying a few ways, I ended up using a few advanced ‘smart select’ techniques – including using tools outside of MS Office – to get all the required data into my Excel:
It’s also a Table in Excel ; note the dropdown arrows on the Headings so you can sort/select from here too. I can also export to a database for further business processing. Or create a nice PDF report in Excel:
If you understand how to use these tools and applications beyond the basics, then what seems like a difficult/complex/manual task, can hopefully be done in a much smarter and more efficient way. And certainly not having to sit down and do over 200 (times 4) manual copy’n’pastes!