The code checked the last for characters of each line to see if they were
numeric. If so it assumed it was a Zip code and that line marked the end of
the address.

What you describe sounds as if it never found a single ZIP, making me wonder
if the lines are padded with spaces to the right. Select one of the lines
that ends witha ZIP code, press the F2 key, and see if the cursor appears off
to the right of the last character. If so, it's padded with spaces,a nd a
slight modification to the code should fix it.

Change this line

If Not IsNumeric(Right(rngSrc.Text, 4)) Then

to

If Not IsNumeric(Right(Trim(rngSrc.Text), 4)) Then

and then re-run it to see what happens


"MG" wrote:

It successfully placed A1 in C1, A2 in D1, and A3 in E1 (the first record is
a 3-line record) but then placed A4 in F1, A5 in G1 and A6 in H1 (the second
record is also a 3-line record). It ran into the 256-column limit,
apparently, and returned runtime error 1004 for application-defined or
object-defined error. It's definitely on the right track, though. The
records referred to in the 256 columns do include both 3- and 4-line records,
just no new row for each record.

Would it be easier to write a piece of code that would insert a blank line
after each 3-line record, and then use transpose to get the 4 column list?