Moving bulk information into Excel

Annieonline

New member
Joined
Feb 6, 2013
Messages
3
Reaction score
0
Points
0
I am trying to help a colleague handle information that currently populates a 732 page PDF document, so my first thought was Excel. I saved the document into Excel format, but (a) roughly 50% of this transferred information is useless and (b) what I do want to work with dumps into one column more than 50,000 rows down. :scared: I have gone back to the original PDF and saved it as a Word document, so that I can edit out the useless information. This will leave me with the names and addresses that I am wanting to pull, but they all go into rows in the first column. Is there any way I can break them into 3 columns for every three rows? (Name, address, city - they currently look like an address label prior to being dumped into Excel)

I am familiar with Excel basics - but don't know if what I am aiming for is even possible. If I could get this information into a spreadsheet to set up a Mail Merge, it would save my colleague many hours of mind numbing manual manipulation. Any ideas or suggestions gratefully recieved, thanks!
 
Can you post an example of the useful data, and explain/show what it should look like. It looks like Text to Columns might help, but it would be good to see the data.
 
Can you post an example of the useful data, and explain/show what it should look like. It looks like Text to Columns might help, but it would be good to see the data.

The data in my Word document looks like an address label, as below. When I save it as an Excel spreadsheet, all the information dumps into sequential rows. I would like to put this into three seperate columns for NAME, ADDRESS and CITY so that I can make a Mail Merge out of the data, but not sure how I can manipulate it without manually cutting and pasting it into the columns.

Doe John James
234 Blue Jay Way
Campbell River, BC V9N 3Y7

Friar Jane Bea
746 Credential Crescent
Campbell River, BC V9N 3Y7

Bloe Frank William Bloe Shirley Leigh
8464 Franklin Blvd
Campbell River, BC V9N 3Y7

Thanks!
 
You could run this code on the data after you get it int Excel

Code:
Public Sub ProcessData()Dim lastrow As Long
Dim i As Long


    Application.ScreenUpdating = False
    
    With ActiveSheet
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = lastrow To 1 Step -4
        
            .Cells(i, "A").Copy .Cells(i - 1, "B")
            .Cells(i - 1, "A").Resize(, 2).Copy .Cells(i - 2, "B")
            .Rows(i - 1).Resize(2).Delete
        Next i
    End With
    
    Application.ScreenUpdating = True
End Sub
 
I am learning on the fly to try and get something put together, so I may have done something wrong...I thought you must mean to run this as a VBA script macro, so I set it up to test it - but cannot get it to run. It keeps getting a syntax error on the first line [Public Sub ProcessData()Dim lastrow As Long] and I'm not certain if this is because my macro name shows up in the very start > Sub MacroName, then the script underneath. It was fun learning to run VBA macro scripts, but I don't know enough to troubleshoot them - yet :confused2:

Thanks

You could run this code on the data after you get it int Excel

Code:
Public Sub ProcessData()Dim lastrow As Long
Dim i As Long


    Application.ScreenUpdating = False
    
    With ActiveSheet
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = lastrow To 1 Step -4
        
            .Cells(i, "A").Copy .Cells(i - 1, "B")
            .Cells(i - 1, "A").Resize(, 2).Copy .Cells(i - 2, "B")
            .Rows(i - 1).Resize(2).Delete
        Next i
    End With
    
    Application.ScreenUpdating = True
End Sub
 
That is because the code did not post properly. The code 'Dim lastrow As Long'​ should be on a line of its own.
 
Back
Top