asked on

How to Convert Address List in Word to Excel

I'm using office 2007.

I have a list of 500ish addresses that someone typed up in word. They need each address to be on its own row in Excel.

This would be relatively simple if the addresses were all the same number of lines long; however, they are not.

Some are 3 lines. Some are 4. Some are 5, 6, even 7 lines long. All of the different length addresses are scattered randomly throughout the document.

Is there an easy way that I am missing to convert this giant list into an Excel document?

I would prefer not to have to separate the different length files into different documents and use "convert text to table" from there.

yo_bee

Off hand I do not think so.
You might need to do some serious editing.

Why would this have ever been created in word to begin with if it was needed in a true row Column format.

I am sorry for this lack of a answer.

nkill

ASKER

I didn't create it.

I'm just being asked to fix it. >_<

yo_bee

:(

They should have asked your first. now you look like some serious formatting.

yo_bee

can you post a sample DOC file?

nkill

ASKER

sure.

names, addresses, and locations changed because the people in the file scare me.
address-sample.docx

nkill

ASKER

There are 500 or so of those addresses and the length in lines varies from 3 to 7.

yo_bee

You could do it if there was some conformity to the lines.
Unfortunately with one set of data have the address in line 2 and others in line 3.

I would recommend that a non-IT person recreate this in a excel sheet or some sort of template that would be easier to format.

You would need some serious logic to work this DOC file to get the desired results.
:(

Saqib Husain

Try this macro from Word while excel is open.

Sub a()
ThisDocument.Select
Debug.Print Len(Selection)
addr = Selection
addr = Replace(addr, vbCr & vbCr, "###")
addr = Replace(addr, vbCr, ",")
addr = Split(addr, "###")
Set xls = GetObject(, "excel.application")
Set ws = xls.activesheet
ws.Range(ws.Cells(1, 1), ws.Cells(UBound(addr) + 1, 1)) = xls.worksheetfunction.transpose(addr)
End Sub

GrahamSkan

It is hard to parse into columns so that, say column 1 = Name, column 2 = Building, Column3 = Road, but this Word macro will put each address in its own row, with each line of the address in a separate column.

Sub CopyToExcel()
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWks As Excel.Worksheet
    Dim r As Integer
    Dim c As Integer
    Dim para As Word.Paragraph
    Dim rng As Word.Range
    Dim strText As String
    Set xlApp = CreateObject("Excel.Application")
    Set xlWbk = xlApp.Workbooks.Add
    
    xlApp.Visible = True
    Set xlWks = xlWbk.Sheets(1)
    r = 1
    c = 1
    For Each para In ActiveDocument.Paragraphs
        Set rng = para.Range
        rng.MoveEnd wdCharacter, -1
        If Len(rng.Text) = 0 Then
            c = 1
            r = r + 1
        Else
            xlWks.Cells(r, c).Value = rng.Text
            c = c + 1
        End If
    Next para
        
    'xlWbk.SaveAS filename
    'xlWbk.Close
    'xlApp.Quit
End Sub

Open in new window

yo_bee

Since the Word Doc does not have any set pattern there will still need a tremendous amount of manual formatting and manipulating to get the data in a logical table (DB) format.

The nice thing about getting this into an excel table at least each row will contain each grouping.

Patrick Matthews

It is possible to improve a bit on the suggestions thus far by using Regular Expressions or some other text parsing techniques, but any method is going to require lots of manual cleanup.

And as for entries with 7 lines: Just. Don't. If these are US addresses, then there should not ever be more than 5 lines.

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Helen Feddema

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick Matthews

Helen_Feddema,

Point taken. It's unusual, but not unheard of.

That said, I despair of having to figure out the logic of where to place everything in a 7-line entry :)

Patrick