Pete,

Put your list into column A of one sheet. On another sheet, starting in Cell A1 and going across
Row 1, put the identifiers for the data that you want to extract, for example:

LC Control Number
Published/Created
Personal Name
ISBN
Dewey Class No.

These whould be entered into A1, B1, C1, D1, E1.

Then select those cells (you can have as many as you want, as long as the strings appear within your
database) and name them "Headers" (Select them, then use Insert / Names... Define...)

Then select your sheet with the database of values, and run the macro below.

If you cannot get it to work, I will send you a working example with the sample data you posted.

HTH,
Bernie
MS Excel MVP

Sub MakeDataBase()
Dim myData As Range
Dim myArea As Range
Dim myCell As Range
Dim i As Integer
Dim myRow As Long

myRow = 2

Set myData = ActiveSheet.Range("A:A").SpecialCells(xlCellTypeCo nstants, 2)

For Each myArea In myData.Areas
For Each myCell In myArea
For i = 1 To Range("Headers").Cells.Count
If InStr(1, myCell.Value, Range("Headers").Cells(1, i).Value) 0 Then
Range("Headers").Parent.Cells(myRow, i).Value = _
Trim(Replace(myCell.Value, Range("Headers").Cells(1, i).Value, ""))
End If
Next i
Next myCell
myRow = myRow + 1
Next myArea

End Sub



"Pete" wrote in message
...
Example given of a variable length (library) list, but which may actually be
1000 long. How can I transpose the data into a Table in either Excel or
Access?

LC Control Number 79013607
Published/Created "New York Seabury Press, 1978, c1977."
Personal Name Boros, Ladislaus, 1927-1981.
ISBN 816404402
Dewey Class No. 248/.48/2

LC Control Number 76007353
Personal Name Boros, Ladislaus, 1927-1981.
ISBN 816411999
Dewey Class No. 248/.3

LC Control Number 78052262
Personal Name Boros, Ladislaus, 1927-1981.
ISBN 816421757
Dewey Class No. 231/.4