Help with Word to Excel export

%3CLINGO-SUB%20id%3D%22lingo-sub-2846814%22%20slang%3D%22en-US%22%3EHelp%20with%20Word%20to%20Excel%20export%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2846814%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20to%20start%20off%2C%20I'm%20not%20really%20good%20with%20Excel%20so%20apologies%20if%20I'm%20asking%20for%20something%20that%20can%20be%20easily%20achieved%2C%20but%20I%20am%20working%20on%20a%20certain%20export%20which%20requires%20exporting%20data%20from%20Word%20and%20importing%20it%20into%20Excel%2C%20and%20I'm%20encountered%20some%20issues.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20a%20picture%20of%20the%20word%20file.%20It%20has%20data%20written%20in%20two%20columns.%20There's%202%20pages%2C%20and%20each%20page%20is%20basically%20a%20question.%20I%20would%20like%20these%20to%20appear%20in%20Excel%20so%20that%20each%20line%20is%20a%20column%2C%20and%20each%20question%20is%20a%20new%20row%2C%20and%20each%20new%20line%20into%20a%20new%20column%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20Word%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Knucklez_0-1634227651771.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F317375i1D836CB47B7764F3%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Knucklez_0-1634227651771.png%22%20alt%3D%22Knucklez_0-1634227651771.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20save%20this%20as%20a%20text%20file%20so%20that%20it%20can%20be%20imported%20into%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20import%20this%20in%20Excel%2C%20everything%20is%20in%20one%20column%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Knucklez_1-1634227761472.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F317379iCF0487792108435D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Knucklez_1-1634227761472.png%22%20alt%3D%22Knucklez_1-1634227761472.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20only%20way%20I%20can%20get%20this%20to%20work%2C%20is%20if%20I%20do%20the%20Word%20in%20this%20format%20where%20everything%20is%20together%20and%20each%20part%20is%20separated%20by%20an%20equal%20sign%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Knucklez_3-1634228640814.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F317390iD956E1002219389F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Knucklez_3-1634228640814.png%22%20alt%3D%22Knucklez_3-1634228640814.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20then%20flows%20into%20Excel%20nicely%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Knucklez_2-1634228569667.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F317389i25877FD5FF68FEEB%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Knucklez_2-1634228569667.png%22%20alt%3D%22Knucklez_2-1634228569667.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20see%20though%2C%20that%20last%20Word%20isn't%20user%20friendly%2C%20and%20quite%20hard%20to%20read.%20Is%20there%20a%20way%20to%20keep%20the%20first%20Word%20format%20with%20columns%2C%20and%20export%20it%20to%20Excel%20where%20it%20can%20separate%20each%20question%20(page)%20into%20a%20new%20row%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2846814%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2847936%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Word%20to%20Excel%20export%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2847936%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1185714%22%20target%3D%22_blank%22%3E%40Knucklez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWord%20could%20have%20two%20columns%20in%20two%20different%20ways.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EOne%20would%20be%20through%20formatting%20the%20whole%20page%2C%20each%20paragraph%20%26nbsp%3B(line%20ended%20with%20a%20%22Return%22%20key)%2C%20as%20two%20columns%20but%20continuous%20(as%20a%20two%20column%20book%20might%20have%20it).%3C%2FLI%3E%3CLI%3EThe%20other%20would%20be%20as%20a%20table%20in%20Word%2C%20with%20two%20columns.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EIn%20the%20latter%20case%2C%20unless%20I'm%20mistaken%2C%20you%20could%20just%20copy%20the%20word%20Table%20into%20Excel%20and%20the%20two%20columns%20would%20be%20two%20columns%20in%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20not%20clear%20why%20you're%20exporting%20as%20text%20(csv%20file)%2C%20given%20the%20ease%20with%20which%20data%20can%20go%20back%20and%20forth%20between%20Microsoft%20products%20in%20their%20native%20form.%20Was%20that%20part%20of%20the%20assignment%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFinally%2C%20unless%20there's%20proprietary%20data%20involved%2C%20it%20would%20help%20if%20instead%20of%20pictures%20you%20were%20to%20attach%20the%20actual%20word%20document%2C%20the%20actual%20excel%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2848017%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Word%20to%20Excel%20export%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2848017%22%20slang%3D%22en-US%22%3Ekeyword%20is%20text%20to%20table%3C%2FLINGO-BODY%3E
New Contributor

Hi,

 

Just to start off, I'm not really good with Excel so apologies if I'm asking for something that can be easily achieved, but I am working on a certain export which requires exporting data from Word and importing it into Excel, and I'm encountered some issues.

 

I've attached a picture of the word file. It has data written in two columns. There's 2 pages, and each page is basically a question. I would like these to appear in Excel so that each line is a column, and each question is a new row, and each new line into a new column?

 

This is the Word:

 

Knucklez_0-1634227651771.png

 

I save this as a text file so that it can be imported into Excel.

 

When I import this in Excel, everything is in one column:

Knucklez_1-1634227761472.png

 

The only way I can get this to work, is if I do the Word in this format where everything is together and each part is separated by an equal sign:

Knucklez_3-1634228640814.png

 

It then flows into Excel nicely:

Knucklez_2-1634228569667.png

 

As you can see though, that last Word isn't user friendly, and quite hard to read. Is there a way to keep the first Word format with columns, and export it to Excel where it can separate each question (page) into a new row?

 

EDIT

 

Thanks for you response @mathetes .

 

Just to explain a bit more what the goal is here. So I have a template in Excel that will needs some of the data from these columns, but not all. I'm looking for a solution where power query can be used to get this data into Excel, and then clean it up so it only leaves the relevant data in the right columns, so then it can go straight into the template. Unfortunately at the moment, if I export from Word to Excel the columns as they are (or a table, because originally it does actually come as a table), it moves everything into one column which doesn't separate the columns as they are in Word. 

 

Normally this is sensitive data, but I have edited to make it gibberish for the purpose of the forum. I have attached the Word document, and I have also attached the Excel with the template (the Item List is where the template is). Ultimately, I'm looking for a way to Excel to read each of the column (e.g. option A, option B) as a separate column, and then to see each page in the Word as a separate row. Like I've mentioned before, this seems to only work when I use the method shown in the screenshot, where all the text is in one block and separated by a delimiter. 

 

I realize this is a big confusing, so let me know if you need more information, and thanks for your help.

 

 

4 Replies

@Knucklez 

 

Word could have two columns in two different ways.

 

  1. One would be through formatting the whole page, each paragraph  (line ended with a "Return" key), as two columns but continuous (as a two column book might have it).
  2. The other would be as a table in Word, with two columns.

In the latter case, unless I'm mistaken, you could just copy the word Table into Excel and the two columns would be two columns in Excel.

 

It's not clear why you're exporting as text (csv file), given the ease with which data can go back and forth between Microsoft products in their native form. Was that part of the assignment?

 

Finally, unless there's proprietary data involved, it would help if instead of pictures you were to attach the actual word document, the actual excel spreadsheet.

 

keyword is text to table
@mathetes

Thanks for the reply, I have edited the post with more information.

@Knucklez 

 

I work on a Mac and don't have access to Power Query (sad to say; I'd love it)...frankly it's still not clear, however, what this is all about. So even if I did have PQ abilities, I'm not sure how I'd help.

 

I tried, just for the sake of trying, to use Copy and Paste Special, picking the option under Paste Special to "Transpose"--which would turn the entries in rows into entries in columns. It works, but because you don't have one Option A, for example, under the first question, the two lines take two columns, and so on across the Excel template. That might be a partial solution, but it wouldn't just take your Word column and change it into a row in Excel, not without some manual juggling on your part.

 

The bigger quandary, from my point of view: This really looks like a strange task to even involve Excel. Other than the fact that you've been given an Excel template to fill in, it's not something that really uses Excel for what Excel is good at. All it appears to be doing is taking advantage of cells, cells arrayed in rows and columns. But you know what? Word can do that too, and what you're working with happens to be, coincidentally, "words"! So why not use a word processor?

 

Fortunately for you, there are other experts here who not only have PQ, but are really good with it, so if there is a solution that fits your parameters, just be patient.