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:

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Watch MrExcel Video

Forum statistics

Threads
1,126,961
Messages
5,621,842
Members
415,860
Latest member
jacky1984

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
Top