how do I convert a database-generated list in word into a table?
 

I have a very long list of books, each book title having a series of fields
(library, author, date etc) below it. At the moment this is just one long
vertical list in word, with line breaks between each book. I wish to turn
this into a Excel table so I can order the data against date/library etc.
Each field has a 2 letter code preceding it, and I can import this into excel
and break out the code into a separate column without difficulty. However,
how do I then get it to lay out the data as a table, instead of a single
multi-thousand item list, using the field code column to ascribe data to
columns?

how do I convert a database-generated list in word into a table?
 

You say database-generated list. Why not Export the Database list
directly to an Excel workbook, and skip the messy part.

Hugh Macnair wrote:
I have a very long list of books, each book title having a series of fields
(library, author, date etc) below it. At the moment this is just one long
vertical list in word, with line breaks between each book. I wish to turn
this into a Excel table so I can order the data against date/library etc.
Each field has a 2 letter code preceding it, and I can import this into excel
and break out the code into a separate column without difficulty. However,
how do I then get it to lay out the data as a table, instead of a single
multi-thousand item list, using the field code column to ascribe data to
columns?

how do I convert a database-generated list in word into a table?
 

I can't tell from "etc" how many rows for each book.

Is the length of each book's associated data the same?

i.e. each book consists of 7 rows

Assuming 7 rows as above.

In B1 enter this
formula..........=INDEX($A:$A,(ROWS($1:1)-1)*7+COLUMNS($A:B)-1)

Copy across to H1 then copy B1:H1 down until you get zeros.

When happy, copypaste specialvaluesokesc

Delete column A


Gord Dibben MS Excel MVP

On Thu, 22 Oct 2009 07:43:01 -0700, Hugh Macnair <Hugh
wrote:

I have a very long list of books, each book title having a series of fields
(library, author, date etc) below it. At the moment this is just one long
vertical list in word, with line breaks between each book. I wish to turn
this into a Excel table so I can order the data against date/library etc.
Each field has a 2 letter code preceding it, and I can import this into excel
and break out the code into a separate column without difficulty. However,
how do I then get it to lay out the data as a table, instead of a single
multi-thousand item list, using the field code column to ascribe data to
columns?

how do I convert a database-generated list in word into a table?
 

One more item.

If by "linebreak" you mean a blank row, you will increase the 7 to 8 and
copy across to column I

Delete that column when done.


Gord

On Thu, 22 Oct 2009 10:40:04 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

I can't tell from "etc" how many rows for each book.

Is the length of each book's associated data the same?

i.e. each book consists of 7 rows

Assuming 7 rows as above.

In B1 enter this
formula..........=INDEX($A:$A,(ROWS($1:1)-1)*7+COLUMNS($A:B)-1)

Copy across to H1 then copy B1:H1 down until you get zeros.

When happy, copypaste specialvaluesokesc

Delete column A


Gord Dibben MS Excel MVP

On Thu, 22 Oct 2009 07:43:01 -0700, Hugh Macnair <Hugh
wrote:

I have a very long list of books, each book title having a series of fields
(library, author, date etc) below it. At the moment this is just one long
vertical list in word, with line breaks between each book. I wish to turn
this into a Excel table so I can order the data against date/library etc.
Each field has a 2 letter code preceding it, and I can import this into excel
and break out the code into a separate column without difficulty. However,
how do I then get it to lay out the data as a table, instead of a single
multi-thousand item list, using the field code column to ascribe data to
columns?