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:	37 
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:	23 
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
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    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.


  3. #3
    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

Posting Permissions

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