I have a text-file address listing with a couple of thousand entries of
varying number of lines--some have 3 lines:

Name
Address
City, State Zip

and others have 4:

Name
Etc (like "c/o" or contact name--no particular pattern to this)
Address
City, State Zip

There are no blank lines between records. I don't have any control over the
format of the text file, as it's an extract from another program and I can't
change the way the data is extracted.

I can't use Excel's transpose feature to get a useful spreadsheet because
the records aren't consistent in the number of lines per record. Is there a
way to convert this data to a spreadsheet with 4 columns per record (with a
blank column for the 3-line records)?

You could use a VBA routine, but only if there is some discernible way to
tell when one address ends and the next begins. So...is there a ZIP at the
very end of EVERY SINGLE ADDRESS? Is it always a ZIP or is it sometimes a
ZIP+4? Any non-US addresses using a nonnumeric postal code?

Tell people here how to objectively determine when to end one address and
start another, and you'll probably get some valuable help

"MG" wrote:

I have a text-file address listing with a couple of thousand entries of
varying number of lines--some have 3 lines:

Name
Address
City, State Zip

and others have 4:

Name
Etc (like "c/o" or contact name--no particular pattern to this)
Address
City, State Zip

There are no blank lines between records. I don't have any control over the
format of the text file, as it's an extract from another program and I can't
change the way the data is extracted.

I can't use Excel's transpose feature to get a useful spreadsheet because
the records aren't consistent in the number of lines per record. Is there a
way to convert this data to a spreadsheet with 4 columns per record (with a
blank column for the 3-line records)?

Thanks, Duke. Every address ends in either a ZIP or a ZIP+4.

"Duke Carey" wrote:

You could use a VBA routine, but only if there is some discernible way to
tell when one address ends and the next begins. So...is there a ZIP at the
very end of EVERY SINGLE ADDRESS? Is it always a ZIP or is it sometimes a
ZIP+4? Any non-US addresses using a nonnumeric postal code?

Tell people here how to objectively determine when to end one address and
start another, and you'll probably get some valuable help

"MG" wrote:

I have a text-file address listing with a couple of thousand entries of
varying number of lines--some have 3 lines:

Name
Address
City, State Zip

and others have 4:

Name
Etc (like "c/o" or contact name--no particular pattern to this)
Address
City, State Zip

There are no blank lines between records. I don't have any control over the
format of the text file, as it's an extract from another program and I can't
change the way the data is extracted.

I can't use Excel's transpose feature to get a useful spreadsheet because
the records aren't consistent in the number of lines per record. Is there a
way to convert this data to a spreadsheet with 4 columns per record (with a
blank column for the 3-line records)?

If you need help with where to put VBA code, look at this site

http://www.contextures.com/xlvba01.html
or
http://www.cpearson.com/excel/codemods.htm


Try this code:

Sub ParseAddresses()
Dim rngSrc As Range
Dim rngTgt As Range
Dim intLineNum As Integer

' change the next line to reflect the first cell of your data
Set rngSrc = Range("A1")

Set rngTgt = Range("C1")

intLineNum = 0
Do While True
If Len(rngSrc.Text) = 0 Then Exit Sub

If Not IsNumeric(Right(rngSrc.Text, 4)) Then
' part of the body of the address
rngTgt.Offset(0, intLineNum).Value = rngSrc.Text
intLineNum = intLineNum + 1
Set rngSrc = rngSrc.Offset(1, 0)
Else
rngTgt.Offset(0, 3).Value = rngSrc.Text
intLineNum = 0
Set rngSrc = rngSrc.Offset(1, 0)
Set rngTgt = rngTgt.Offset(1, 0)
' it's PRESUMABLY a zip code & the final address line
End If
Loop

End Sub

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?

"Duke Carey" wrote:

If you need help with where to put VBA code, look at this site

http://www.contextures.com/xlvba01.html
or
http://www.cpearson.com/excel/codemods.htm


Try this code:

Sub ParseAddresses()
Dim rngSrc As Range
Dim rngTgt As Range
Dim intLineNum As Integer

' change the next line to reflect the first cell of your data
Set rngSrc = Range("A1")

Set rngTgt = Range("C1")

intLineNum = 0
Do While True
If Len(rngSrc.Text) = 0 Then Exit Sub

If Not IsNumeric(Right(rngSrc.Text, 4)) Then
' part of the body of the address
rngTgt.Offset(0, intLineNum).Value = rngSrc.Text
intLineNum = intLineNum + 1
Set rngSrc = rngSrc.Offset(1, 0)
Else
rngTgt.Offset(0, 3).Value = rngSrc.Text
intLineNum = 0
Set rngSrc = rngSrc.Offset(1, 0)
Set rngTgt = rngTgt.Offset(1, 0)
' it's PRESUMABLY a zip code & the final address line
End If
Loop

End Sub

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?