Word Tables to Excel when number of rows varies and transpose also needed.

MichelleJAndrew

New Member
Joined
Sep 6, 2019
Messages
1
Hi!
I want to take tables from a Word document and put them into excel where the columns are transposed to rows in a certain way. The word document has three tables, each of which can spread across 3 to around 20 pages. Each table has two columns. The first has a table number which tends to be in the form 14.2.3.1 or 14.4.2 or 14.5 for example for the first table and eg 16.3.4 or 16.7 or 16.7.8.8.7 for the third table (beginning 15s for the second table predictably!).

In excel I want the first column to hold this number, column 2 to hold this number with only the first dot, column 3 to be called Title 1 and hold the first for that table number, Title 2 to hold the second row for that same table number, etc to Title 5.

There are between 1 and 5 rows per cell in column 2 of the word document tables so it looks something like this

14.3 aaaa
bbbb
cccc
____________________________

14.5.6.7 ddddd
_________________________

14.7.6 ee
fff
g
hhhh
__________________________


and I want it to look like this

TABNUM newTABnum title 1 title 2 title3 title 4 title5
14.3 14.3 aaaa bbbb cccc
14.5.6.7 14.567 ddddd
14.7.6 14.76 ee fff g hhhh

The tables are varying number of rows in total and varyingnumber of rows within each cell (1 -5 rows normally, sometimes 7 if there'sextra notes), but always two columns in word.

For removing the dots for newTABnum i'm using replace whichworks most of the time, I might be better using a scan function rather thanguessing how many dots there will be?

I can manually put a transpose formula in at the start ofeach new cell in excel after copying the whole lot in but its quite intensiveso i'm thinking a bit of VBA or a vlookup of some kind that searches for the14s , find a TABNUM then reads each row of a cell (while transposing) intoExcel? I've tried Index and Match combined to which at least gets some of theway there but I'd like to know the 'right' (most efficient way of doing this )

thank you! any suggestions gratefully received as the copying and pasting is taking ages!


EDIT:

14.3.8.1.6Cli
Shift Tab
Poption
Bivaility
Subtitle:
14.3.8nalysis
14.3SI Units)
Saftion

<tbody>
</tbody>


Word table looks like this for example​
 
Last edited by a moderator:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top