HELP: Importing Word Form Data into Excel

Question

Please help!

Use case: I host focus groups and take notes on a single word doc. The focus groups are designed to make recommendations to companies to improve diversity, equity, and inclusion efforts. I ask about 10 questions, and each question has roughly 7 responses. In order to analyze the notes, I have to push the data from the Word doc to Excel.

Strategy: I've create a note-taking template on Word with form fields (pictured), and save as txt file. This way, I can import my data into Excel, comma separate values, and see people's responses by each question asked.  

Issue: When I try to import the data into Excel, everything is in one line (pictured).

Desired outcome: I would want my imported data questions to appear in the top row as column titles, and I would want my imported responses to appear under each corresponding question. I've attached a picture of how I would want this to be displayed. 

If you have a solution or a different work-around, that would be really helpful. I am trying to push the responses to each question I ask in a focus group to Excel, where each response is in a cell under the appropriate question column.

Answer 1

I need to understand your process in detail.

How do your create your input file?

Is it simply a Word document that other people use to input replies to your questions?

Does each person have a separate file

Or do you do the typing? All replies into a single file?

Look, Word does not create text files that Excel can handle gracefully.

As I suggested before, create your table (or multiple tables) with the 3 columns I suggested.

You can insert blank rows into the Word table by right click > Insert command > insert row above (or below current row).

If you are using Word documents with Word tables in them, just copy from word and paste into excel. That is a better way of maintaining the table structure.

.

You say you are using the "Import Wizard". That is old technology.

You say you are selecting "delimited" and Comma. That only works if the TXT file creation tool inserts commas between columns. Word DOES NOT DO THAT! Which is why you are seeing the "data blob".

Could you share some file examples.

Make them simple, 2 or 3 questions, with 2 or 3 repliers each

Share a DOCX, TXT and XLSX copies of the same file.

Hint, since you are going to have multiple files to share, put them into a folder, upload the folder as a single item, then give us a share link to the folder in onedrive.

****************** UPLOAD EXAMPLE - TROUBLE SHOOTING - SHARE PERSONAL ONEDRIVE FILE (NOT BUSINESS ONEDRIVE) .

Trouble shooting problems in files can be like a visit to the dentist, a long, slow painful process of us trying to “extract” the clues needed to recognize the problem in a back and forth flow of questions and answers so that we can come up with a / “the” solution.

.

Often it is faster and easier for everyone if we have a “sample file” get "hands on", to look at, and to “play with”.

.

This next link provides some tips on setting up a sample file and specific instructions for uploading and "sharing" it for us to access:

.

https://answers.microsoft.com/en-us/windows/forum/windows_other-winapps/trouble-shooting-share-onedrive-file/a231a097-bcbf-4e34-ad6c-a33118baf471

The article includes links to macros to randomize text in Word and numbers in Excel to preserve privacy

**************************************

.

This article describes another way of sharing Office files:

2020 03 09- Share a cloud stored document from Microsoft Office
https://office-watch.com/2020/share-cloud-stored-document-microsoft-office/
Using “Invites” to specific people from inside Office apps. Modify document access permissions and add a “note” with “how to” instructions for recipient

Share Tab of Backstage View in PowerPoint 365 for Windows 2020 10 12

https://www.indezine.com/products/powerpoint/learn/interface/365/share.html

Learn about the Share tab of Backstage view in PowerPoint 365 for Windows. You can share via OneDrive, PDF, or as an email attachment.

Applies to other Office 365 applications.

.

Answer 2

Do you need to retain or share the Word document with other people?

Are you open to restructuring the table in Word to make it more Excel friendly?

How do you import the data into Excel from Word?

How about a minor structure tweak:

You can copy and paste this table directly from Word to Excel

What do you do with the data once you put it into Excel?

Have you considered simply doing your data entry directly in Excel?

Answer 3

Hey, thanks for the quick response!

I am needing to retain/share the document.

I am open to restructuring the document to be more Excel friendly, but there are instances where focus groups could have about 10 people giving responses to 17 questions, which takes up a lot of space when formatted as a single table on the word document.

When importing from word to excel, I save the word doc as a .txt file, and I specify that I would like to preserve only the form data. Then in excel I select "import data" and on the import wizard I select "delimited" > check only "comma" > press "finish." Then as was shown in the original post, everything is in a single row.

Now I'm thinking that I should just use a code in excel to extract table data from the word doc and into the excel sheet.

Answer 4

Hi EGgarc027

Please, check the video below. It has the answer and solution you are looking for.

Do let me know if you need more help

Regards

Jeovany

https://www.youtube.com/watch?v=nXeYv-W-Wt4