I have a list of address data - all in one column. How to separate?

louisebrain

New member
Joined
Aug 20, 2014
Messages
1
Reaction score
0
Points
0
Hi everyone. Please bare with me a my excel knowledge is very basic. I have a list of addresses, all in one column. This is a representation of how it looks now:

excel-help.jpg

What I need it to look like is this:

excel-help-2.jpg

I have no experience with macros or scripts so I'm hoping that someone has a formula - or that they can walk me through the process.

Many thanks in advance
 
Assuming your original table is in Sheet1, occuppying A:B, then in a separate sheet try:

=INDEX(Sheet1!$B:$B,5*(ROWS(Sheet1!$A$1:$A1)-1)+COLUMNS(Sheet1!$A$1:A$1))

copied across 4 columns and down as far as needed.

Adjust ranges to suit first.
 
Code:
Sub Reformat()Dim lastrow As Long
Dim i As Long


    Application.ScreenUpdating = False
    
    With ActiveSheet
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = lastrow - 3 To 1 Step -5
        
            .Cells(i, "E").Value = .Cells(i + 3, "B").Value
            .Cells(i, "D").Value = .Cells(i + 2, "B").Value
            .Cells(i, "C").Value = .Cells(i + 1, "B").Value
            .Cells(i, "B").Value = .Cells(i, "B").Value
            
            .Rows(i + 1).Resize(4).Delete
        Next i
        
        .Columns(1).Delete
        .Rows(1).Insert
        .Range("A1:D1").Value = Array("Company", "Town", "PostCode", "Phone")
        .Columns("A:D").AutoFit
    End With
    
    Application.ScreenUpdating = True
End Sub
 
Back
Top