I received lots of mailing lists that are saved as a Word .doc, and are
formatted as mailing labels. But I need them in either a .csv or .xls
format. The problem is that when attempting to save the .doc it maintains
the address information in a block and doesn't it place all the address
information in one line and then all the information for the next on it's own
line. I know that there is a simple way to go from a spreadsheet into a
mailing label .doc, but what about the opposite? I've tried using macros,
but the line count isn't always the same for each address. Any suggestions,
etc. would be greatly appreciated.

Steve

Is there a gap (blank row(s)) between each address group?

Is there some indicator that you could pick off.

Second line of address always begins with a number--but no other lines do that?



Stringy wrote:

I received lots of mailing lists that are saved as a Word .doc, and are
formatted as mailing labels. But I need them in either a .csv or .xls
format. The problem is that when attempting to save the .doc it maintains
the address information in a block and doesn't it place all the address
information in one line and then all the information for the next on it's own
line. I know that there is a simple way to go from a spreadsheet into a
mailing label .doc, but what about the opposite? I've tried using macros,
but the line count isn't always the same for each address. Any suggestions,
etc. would be greatly appreciated.

Steve

--

Dave Peterson

"Stringy" wrote in message
...
I received lots of mailing lists that are saved as a Word .doc, and are
formatted as mailing labels. But I need them in either a .csv or .xls
format. The problem is that when attempting to save the .doc it maintains
the address information in a block and doesn't it place all the address
information in one line and then all the information for the next on it's
own
line. I know that there is a simple way to go from a spreadsheet into a
mailing label .doc, but what about the opposite? I've tried using macros,
but the line count isn't always the same for each address. Any
suggestions,
etc. would be greatly appreciated.

Steve

Can you open the csv file in notepad and paste a releavant portion of it?

/Fredrik

Dave, thanks for your reply. Many times there will be between 1-3 blank
lines, but not always. They don't consistently have a name and a company
name or only 1 address line. Everything seems to vary, which is what makes
it annoying. I would say that the only constant is that the address group
always ends in a zip code (unfortunately it may be in either 5 digit or Zip+4
format mixed throughout the addresses).
I guess one option would be blank rows, because at most 2 address groups are
continuous without a gap. Once the conversion is done, I could simply scroll
and find any really long lines.
I also thought of another associated problem. After manually converting the
data it doesn't always line up correctly in the columns (i.e. all the company
names in one column, all the addresses in another, city in another, etc.),
because not all the address blocks have the same amount of lines. So I have
to go through the addresses and manually move the data from one column to
another.

"Dave Peterson" wrote:

Is there a gap (blank row(s)) between each address group?

Is there some indicator that you could pick off.

Second line of address always begins with a number--but no other lines do that?



Stringy wrote:

I received lots of mailing lists that are saved as a Word .doc, and are
formatted as mailing labels. But I need them in either a .csv or .xls
format. The problem is that when attempting to save the .doc it maintains
the address information in a block and doesn't it place all the address
information in one line and then all the information for the next on it's own
line. I know that there is a simple way to go from a spreadsheet into a
mailing label .doc, but what about the opposite? I've tried using macros,
but the line count isn't always the same for each address. Any suggestions,
etc. would be greatly appreciated.

Steve

--

Dave Peterson

If the zip code line is the only line that ends with those numeric characters
(12345 or 12345-1234), then you could use that as the key. When you see it, you
know the next line is the start of a new address.

But that second point is the killer. You could have lots of manual work to do.
(I don't know any other way except for moving stuff manually. I think it takes
a human being to notice all the different fields/formats.)

(I feel your pain!)

Stringy wrote:

Dave, thanks for your reply. Many times there will be between 1-3 blank
lines, but not always. They don't consistently have a name and a company
name or only 1 address line. Everything seems to vary, which is what makes
it annoying. I would say that the only constant is that the address group
always ends in a zip code (unfortunately it may be in either 5 digit or Zip+4
format mixed throughout the addresses).
I guess one option would be blank rows, because at most 2 address groups are
continuous without a gap. Once the conversion is done, I could simply scroll
and find any really long lines.
I also thought of another associated problem. After manually converting the
data it doesn't always line up correctly in the columns (i.e. all the company
names in one column, all the addresses in another, city in another, etc.),
because not all the address blocks have the same amount of lines. So I have
to go through the addresses and manually move the data from one column to
another.

"Dave Peterson" wrote:

Is there a gap (blank row(s)) between each address group?

Is there some indicator that you could pick off.

Second line of address always begins with a number--but no other lines do that?



Stringy wrote:

I received lots of mailing lists that are saved as a Word .doc, and are
formatted as mailing labels. But I need them in either a .csv or .xls
format. The problem is that when attempting to save the .doc it maintains
the address information in a block and doesn't it place all the address
information in one line and then all the information for the next on it's own
line. I know that there is a simple way to go from a spreadsheet into a
mailing label .doc, but what about the opposite? I've tried using macros,
but the line count isn't always the same for each address. Any suggestions,
etc. would be greatly appreciated.

Steve

--

Dave Peterson


--

Dave Peterson

Dave, when you mention using this as my key are you referring to an automated
process or simply visually looking for it?

"Dave Peterson" wrote:

If the zip code line is the only line that ends with those numeric characters
(12345 or 12345-1234), then you could use that as the key. When you see it, you
know the next line is the start of a new address.

But that second point is the killer. You could have lots of manual work to do.
(I don't know any other way except for moving stuff manually. I think it takes
a human being to notice all the different fields/formats.)

(I feel your pain!)

Stringy wrote:

Dave, thanks for your reply. Many times there will be between 1-3 blank
lines, but not always. They don't consistently have a name and a company
name or only 1 address line. Everything seems to vary, which is what makes
it annoying. I would say that the only constant is that the address group
always ends in a zip code (unfortunately it may be in either 5 digit or Zip+4
format mixed throughout the addresses).
I guess one option would be blank rows, because at most 2 address groups are
continuous without a gap. Once the conversion is done, I could simply scroll
and find any really long lines.
I also thought of another associated problem. After manually converting the
data it doesn't always line up correctly in the columns (i.e. all the company
names in one column, all the addresses in another, city in another, etc.),
because not all the address blocks have the same amount of lines. So I have
to go through the addresses and manually move the data from one column to
another.

"Dave Peterson" wrote:

Is there a gap (blank row(s)) between each address group?

Is there some indicator that you could pick off.

Second line of address always begins with a number--but no other lines do that?



Stringy wrote:

I received lots of mailing lists that are saved as a Word .doc, and are
formatted as mailing labels. But I need them in either a .csv or .xls
format. The problem is that when attempting to save the .doc it maintains
the address information in a block and doesn't it place all the address
information in one line and then all the information for the next on it's own
line. I know that there is a simple way to go from a spreadsheet into a
mailing label .doc, but what about the opposite? I've tried using macros,
but the line count isn't always the same for each address. Any suggestions,
etc. would be greatly appreciated.

Steve

--

Dave Peterson


--

Dave Peterson

I was thinking for a macro.

Option Explicit
Sub testme()
Dim curWks As Worksheet
Dim newWks As Worksheet

Dim TopCell As Range
Dim BotCell As Range
Dim rCtr As Long
Dim DestCell As Range
Dim LastRow As Long

Dim myStr As String

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

Set DestCell = newWks.Range("A1")

With curWks
Set TopCell = .Range("a1")
Set BotCell = .Range("a1")

'add a dummy value--to make sure there's a final zip.
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Cells(LastRow, "A").Value = "asdf12345"

For rCtr = 1 To LastRow
'remove trailing spaces, too.
myStr = Trim(.Cells(rCtr, "A").Value)
If Right(myStr, 5) Like "#####" _
Or Right(myStr, 10) Like "#####-####" Then
Set BotCell = .Cells(rCtr, "a")
.Range(TopCell, BotCell).Copy
DestCell.PasteSpecial Transpose:=True
Set DestCell = DestCell.Offset(1, 0)
Do
Set TopCell = BotCell.Offset(1, 0)
If TopCell.Row LastRow Then
Exit For
End If
Set BotCell = TopCell
If IsEmpty(TopCell) Then
'stay in loop
Else
Exit Do
End If
Loop
End If
Next rCtr
'clean up dummy cell on original sheet
.Cells(LastRow, "A").ClearContents
End With

'clean up dummy row on new worksheet.
With newWks
.Cells(.Rows.Count, "A").End(xlUp).EntireRow.Delete
End With

End Sub

But this doesn't address the second part of the problem.



Stringy wrote:

Dave, when you mention using this as my key are you referring to an automated
process or simply visually looking for it?

"Dave Peterson" wrote:

If the zip code line is the only line that ends with those numeric characters
(12345 or 12345-1234), then you could use that as the key. When you see it, you
know the next line is the start of a new address.

But that second point is the killer. You could have lots of manual work to do.
(I don't know any other way except for moving stuff manually. I think it takes
a human being to notice all the different fields/formats.)

(I feel your pain!)

Stringy wrote:

Dave, thanks for your reply. Many times there will be between 1-3 blank
lines, but not always. They don't consistently have a name and a company
name or only 1 address line. Everything seems to vary, which is what makes
it annoying. I would say that the only constant is that the address group
always ends in a zip code (unfortunately it may be in either 5 digit or Zip+4
format mixed throughout the addresses).
I guess one option would be blank rows, because at most 2 address groups are
continuous without a gap. Once the conversion is done, I could simply scroll
and find any really long lines.
I also thought of another associated problem. After manually converting the
data it doesn't always line up correctly in the columns (i.e. all the company
names in one column, all the addresses in another, city in another, etc.),
because not all the address blocks have the same amount of lines. So I have
to go through the addresses and manually move the data from one column to
another.

"Dave Peterson" wrote:

Is there a gap (blank row(s)) between each address group?

Is there some indicator that you could pick off.

Second line of address always begins with a number--but no other lines do that?



Stringy wrote:

I received lots of mailing lists that are saved as a Word .doc, and are
formatted as mailing labels. But I need them in either a .csv or .xls
format. The problem is that when attempting to save the .doc it maintains
the address information in a block and doesn't it place all the address
information in one line and then all the information for the next on it's own
line. I know that there is a simple way to go from a spreadsheet into a
mailing label .doc, but what about the opposite? I've tried using macros,
but the line count isn't always the same for each address. Any suggestions,
etc. would be greatly appreciated.

Steve

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson