Converting Word labels into Excel

[jwatschke]

How do you convert Microsoft Word 2000 labels into Excel 2000? The labels list name and address. I would like to take this information and make columns in Excel (one column for name, one for street address, one for city and state).

 

[Damon Ostrander]

Hi jwatschke,

Converting generally requires a macro--either in Excel or in Word. And this macro must be based on the method used to generate/format the labels list. For example, if you use Avery label sheets and the Avery label wizard to generate your labels in Word, then the addresses will be in a table--each table being one sheet--and each address will be in a cell of the table. But it is also possible to create an address list in Word using tabs, etc.

Another thing: in general addresses do not adhere to one line (paragraph in Word) for name and address fields. For example, to address someone within a business a business name as well as individual's name may be required. Similiarly with addresses: if an apartment of suite is specified, this is often put on a separate line so that two address lines are required. I requires a lot of "smarts" to correctly parse addresses into name, street address, city and state taking into account all these variations. So you may find yourself having to manually correct Excel entries that have been incorrectly parsed.

Once you specify what kind of label application or formatting you are using I'm sure someone already has code they could provide to do this.

Dreamboat, are you there?

 

[jwatschke]

I have no experience in scripting. I was hoping there would be a wizard to follow-through in order to accomplish this. I am familiar with the Mail Merge Wizard and I was hoping there would be an easy way to use it in reverse.
Thanks for your input.

 

[Anne Troy]

Thanks, Damon, for bringing this to my attention.

Here's the non-macro way. I'm still looking for the macro.

http://www.gmayor.com/convert_labels_into_mail_merge.htm

 

[Anne Troy]

I lied. I thought the MVPs had a macro, but it looks the same or very similar. I'll let you decide:

http://word.mvps.org/FAQs/MailMerge/ConvertAdrsToDatafile.htm

There's a macro in my book for it, so I'll paste it here, but I don't code. Maybe Damon can help if you have difficulty.

Scenario
Suzy is to collect from each salesperson their lists of clients for sending holiday cards. She needs to combine the lists because it needs to be sorted in Zip Code order.
John sends a Word file full of labels to Suzy with a note that states “Here’s my list from last year.”
Suzy wonders how she’s going to integrate John’s label formatted addresses into the existing data list.
Solution
This macro is courtesy of my friend, Tom Giaquinto. After having manually converted many label files back to data source layouts, I can tell you that this macro can save you a great deal of time. This macro actually takes a mailing label-formatted document and converts it into a real data file, regardless of how many lines of text are on each label. Questions on ways to perform this task quickly often come around the holidays when people are constructing holiday card mailing lists.
Type the following code into an inserted Module in your normal.dot and you’ll be able to run the code on any Word document.

Sub DataSorcerer()

  Dim tbl As Table  'Table object
  Dim x As Integer, y As Integer  'Counters
  Dim intRows As Integer  'Total rows in table
  Dim intCols As Integer  'Total Columns in table
  Dim intGotVal As Integer  'Test for blank cells

  'Select Entire Document and replace all line breaks
  'with temporary place holder
  Selection.WholeStory
  With Selection.Find
    .Text = "^p"
    .Replacement.Text = "<REPLACEMENT>"
  End With
  Selection.Find.Execute Replace:=wdReplaceAll

  Selection.WholeStory
  With Selection.Find
    .Text = "^l"
    .Replacement.Text = "<REPLACEMENT>"
  End With
  Selection.Find.Execute Replace:=wdReplaceAll

  'Convert all tables to text w/ paragraph symbols
  'used as delimiters
  For Each tbl In ActiveDocument.Tables
    tbl.Select
    tbl.Rows.ConvertToText Separator:=wdSeparateByParagraphs
  Next tbl

  'Remove all section breaks from document
  Selection.WholeStory
  With Selection.Find
    .Text = "^b"
    .Replacement.Text = "^p"
  End With
  Selection.Find.Execute Replace:=wdReplaceAll

  'Replace all double line breaks w/ single line breaks
  Selection.WholeStory
  With Selection.Find
    .Text = "^p^p"
    .Replacement.Text = "^p"
  End With
  Selection.Find.Execute Replace:=wdReplaceAll

  'Replace temporary place holder w/ tabs to be used
  'when text is converted to table
  Selection.WholeStory
  With Selection.Find
    .Text = "<REPLACEMENT>"
    .Replacement.Text = "^t"
  End With
  Selection.Find.Execute Replace:=wdReplaceAll

  'Select entire document then convert to table w/ tabs
  'as delimiter
  Selection.WholeStory
  Selection.ConvertToTable Separator:=wdSeparateByTabs

  'Count rows and columns and set current table object
  intRows = ActiveDocument.Tables(1).Rows.Count
  intCols = ActiveDocument.Tables(1).Columns.Count
  Set tbl = ActiveDocument.Tables(1)

  'Check for and remove blank rows
  For x = 1 To intRows
    intGotVal = 0
    For y = 1 To intCols
      If tbl.Cell(x, y).Range.Characters.Count > 1 Then
        intGotVal = intGotVal + 1
          y = intCols
      Else
        'Do Nothing
      End If
    Next y

      If intGotVal = 0 Then
        tbl.Rows(x).Delete
        x = x - 1
      Else
        'Do Nothing
      End If
  Next x

  'Check for and remove blank columns
  For y = 1 To intCols
    If y > tbl.Columns.Count Then
      y = intCols
    Else
      intGotVal = 0
      For x = 1 To intRows
        If tbl.Cell(x, y).Range.Characters.Count > 1 Then
          intGotVal = intGotVal + 1
          x = intRows
        Else
         'Do Nothing
        End If
      Next x

      If intGotVal = 0 Then
        tbl.Columns(y).Delete
        y = y - 1
      Else
        'Do Nothing
      End If
    End If
  Next y

  'Reset column count and insert blank row
  'adding a column heading to each cell
  intCols = tbl.Columns.Count
  tbl.Rows(1).Select
  Selection.InsertRows 1
  For y = 1 To intCols
    tbl.Cell(1, y).Select
    Selection.Text = "Line" & y
  Next y

End Sub

 

[jwatschke]

Thank you all so much for your input. The article on "word.mvps.org" was exactly what I needed. I was finally able to export those Word labels into Excel without too much of a headache.

Thank you for your attention to my first post on this forum!

 

[Damon Ostrander]

...and thanks Dreamboat.

You're a dreamboat.

Damon

 

[Anne Troy]

Anytime, Damon.

Thanks for bringing me in.
I want to help where I can but there's so many questions here, I don't get to see them all.
I usually just hit the "unanswered posts" link...