Results 1 to 3 of 3

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

  1. #1

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

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:

    Click image for larger version. 

Name:	excel-help.jpg 
Views:	38 
Size:	37.6 KB 
ID:	2568

    What I need it to look like is this:

    Click image for larger version. 

Name:	excel-help-2.jpg 
Views:	25 
Size:	92.4 KB 
ID:	2569

    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

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Mississauga, Canada
    Excel Version
    Excel 2016
    Assuming your original table is in Sheet1, occuppying A:B, then in a separate sheet try:


    copied across 4 columns and down as far as needed.

    Adjust ranges to suit first.

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    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
            .Range("A1:D1").Value = Array("Company", "Town", "PostCode", "Phone")
        End With
        Application.ScreenUpdating = True
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts