Great, I'll get myself a copy for an author or two and play around with it as
I have time and see what bells go off in my head. May not be very fast in
coming, someone just backed a 5-ton dump truck up to my desk at work and just
dumped it all there yesterday, and unfortunately my job is not always just
8-5.

JLatham

"Pete" wrote:

Thanks, I have been travelling for two days, hence delay.

I post the link to Library of Congress. Click 'Author' and enter, e.g.,
Boros, etc

I then download the Full text version. The Marc format would obviously be
more sophisticated, but one step at a time.

If you have any further ideas I remain highly interested.
Meanwhile I will test out what you have written in your response. It is
certainly the track I want to travel down.

Pete

http://catalog.loc.gov/cgi-bin/Pwebr...cal&PAGE=First

"JLatham" wrote:

Have you got a link to that document? That would be nice to look at along
with this.

Having it already in Excel makes it pretty nice. Your plan to start by
splitting each current row into columns, etc. is a good, logical way to
approach it.

Here are 2 formulas that should give you a leg up on it, assume first "LC
Control Number 79013607" is on row 1, then in B1 enter this formula:
=IF(ISERR(TRIM(LEFT(A1,FIND(" ",A1)))),"",TRIM(LEFT(A1,FIND(" ",A1))))

The IF(ISERR( portion keeps #VALUE from appearing when you fill this down
the sheet and cell in column A is empty. There are 2 spaces between the
double quotes with then FIND statement, so we look for 2 location of 2 spaces
within the text in An (n being any given row number) and just keep what's to
the left of them.

In C1, type this formula:
=TRIM(RIGHT(A1,LEN(A1)-LEN(B1)))

that will then get the 2nd half of whatever is in column A and strip any
leading/trailing blanks from it. Don't know if what you put in your first
post was copied from your Excel entries or not, but I copied them from the
post into an Excel sheet here and applied the formulas and they worked well.
Only odd thing I noted was that the name, Boros, Ladislaus, ends up with what
looks like a couple of leading spaces - they are not true space characters,
just some odd, 'invisible' characters.

Fill those formulas on down the sheet and you will have started breaking up
the text into two parts.

To get the displayed information in columns B and C to change from a formula
result to an actual value, you can select them and use Copy followed by Edit
| Paste Special and choose the [Values] option. Turns formula results into
values. Then you could do away with column A - or use the Edit | Paste
Special [Values] to copy them to another sheet for more processing if you get
concerned about losing the original information in column A.

"Pete" wrote:

Information illustrated is a text file from Library of Congress Internet
database pasted into Excel. There are many variables (about a dozen, though
the majority would be consistent). I have been thinking that if I can grasp
the principle with a few I can then apply it to a wider selection. I would
start by separating the text in column one so that the label (e.g., LC
Control Number would be in column A; and the actual number would be in column
B)

I want to end up with the Labels in column A being transposed as column
headings along the top row of an Excel spreadsheet, which would be linked to
the MS Access database for import. The data then needs to be added to the
rows beneath the column headings. This is the difficult bit. I can do it for
one set of data, but not for subsequent ones, especially when the row of
headings may not be exactly the same. (Of course, all possible could be types
along the top row beforehand if this makes it more feasible).

Thanks,

Pete