You are here:

Excel/Extract answers and generate summary list of answers



Please let me ask for your assistance. I have a long MS Word document consisting of ~400 questions that I am trying to semi-automate a process for extracting a summary of the answers.

For each question there is:
- a heading (e.g.: Question A1: Question A Heading)
- the question (variable length)
- a short (one word) answer (formatted: ANSWER: {answer})
Note the short answer can take one of five text values (e.g.: A, B, C, D or E)
- a detailed answer (variable length)

To facilitate automatic generation of a summary listing of the short answers for the entire document, I plan to have an Excel document with all ~400 question headings listed in Column A of Sheet1.

The planned process is:
1) Manually copy the entire document into Sheet2
2) Use VBA code to step through the listing in Column A of Sheet1
3) For each question heading in Sheet1, find the corresponding text in Sheet2 and copy the corresponding short answer into Sheet1.
(a) For a given question heading in Sheet1, Column A, find the corresponding text in Sheet2
(b) Search for the text "ANSWER:" that follows that question heading
(c) extract the one word answer that immediately follows "ANSWER:" and
(d) Copy that answer into the corresponding row in Sheet 1, Column B

I have VBA code, for example, that can pull capitalized strings from text copied into a worksheet, but I am trying to figure out how to adapt that.  

Thank you very much for any guidance you can lend.




If you want to send me the excel file I will take a look.  Make sure you explain exactly what you want done.  If you think your above explanation is sufficient, then I will let you know what I don't understand if I disagree.  

send to

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 All rights reserved.