Wow,
I am a little confused for sure, but... You can concatenate on a single
sheet. = A2 & B2 & C2 & Z2, will combine into whatever cell you want. No
code is necessary. If you have values that are text, then = Val(A1), will
make it into a value.

After you have your concatenated information, do a copy, then paste
speical/value. This will get rid of the formulas. No VBA needed. The formula
created in the destination cell can be copied down to rows that you are
trying to capture.

Hope this was helpful. I am not sure what the purpose of going to the second
sheet was serving. But if there is no data in a cell, but data in the "next"
cell, meaning the data is of variable length in a row, this will still work,
the blank will come in as a blank, and what is after will still come in.

David

" wrote:

I wonder if some kind soul would help me write the following macro.
I'm an expert programmer (in some universe), so I don't need much hand-
holding. But I'm not familiar with Excel/VBA objects, and my book is
a poor reference text.

I need help with syntax, not the program logic. I don't need tested
syntax. Just something close enough that I can look up idioms to
debug mistakes and refine the implementation. In other words, I don't
expect a turn-key solution or someone to do all the work for me.
Where's the fun in that? ;-) But I do hope someone can take 5-10
minutes to help me with the VBA idioms. Thanks.

Basically, I need to reformat data that was columnarized in the
original PDF, but when I saved it as text and import it into Excel,
each word is put into a separate cell. I don't have OCR software, so
I don't believe there is any other way for me to get the data into
Excel.

My thought is to write the following macro. Of course, if there is a
better way, I'm all ears -- or should I say "all eyes"? :-)

' copy Sheet1 to Sheet2, changing format as we go
for each row of cells in selection
newRow = next row in Sheet2
copy columns A:D of selected row into newRow columns A:D
copy last 2 columns of selected row into newRow columns F:G
concatenate cells between first 4 and last 2 columns and put it
into newRow column E

where "selection" is all of Sheet1. I can do "selection" the hard way
(actually select all cells). But if there is a way to refer to the
limits of the worksheet without highlighting them, so much the better.

I could go into details about what idioms I need. But if I were on
the receiving end of this question, it would be easier for me to
simply write the above algorithm in "approximate" VBA.

That's what I am hoping some kind soul will do for me. No need to
spend more than 5-10 min. No need to test it or even to enter it into
the VBA editor. I'll take care of all that, if you can just give me a
running start. (Of course, if you want to do more, that's up to you.)

Thanks again. FYI, this is not a student assignment. I saved a PDF
from a web site, and I want to sort and analyze the information using
Excel. I had hoped to use the "fixed width" format to Import External
Data. But I cannot coerce my revision of Adobe Reader to save the PDF
in columnarized text. Save Text collapses all whitespace into one
space, which is a problem because one of the PDF columns contains
multiword entries.

So I'm looking for a "quick and dirty" solution. Problem is: I'm not
conversant enough in VBA to do anything "quick". If I could use C on
a UNIX derivative, I would have been done in the time it took me to
compose this posting. (Well, almost.)

In the meantime, I will pick through my (poor) VBA book to see if I
can figure this out myself. It's just that experience has taught me
that "the answer is out there", but painfully hard to find in that
book (the most-often recommended one, BTW). I have to go out for
dinner now. I'm betting that some kind person in aother timezone can
provide an "approximate" solution before I return.

And once again, many thanks for your indulgence.