[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

Convert column of addresses into multiple columns for each address element

I've imported a list of addresses into Excel and each line is listed in individual cells in 1 column.

I'd like to easily separate this out into manageable columns.  for instance.

it lays out like.

organization
person
address
city state zip
phone
organization
person
address
city state zip
phone
...

I'd like to convert to columns so it lays out

organization   person   street   city state zip  phone
organization   person   street   city state zip  phone

I can convert so that each line is separated by comma or whatever, but can't get it to convert.  Its 415 rows so not terrible but more than I want to handle manually.

Advice?
0
axessJosh
Asked:
axessJosh
  • 3
  • 2
1 Solution
 
nutschCommented:
Assuming obviously that all your data has five rows, this code will do that:

Sub TurnRowsToColumns()
Dim shtOrg As Worksheet, shtDest As Worksheet
Dim lLastRow As Long, lRowLoop As Long

'turn off updates to speed up code execution
With application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With


Set shtOrg = ActiveSheet
Set shtDest = Sheets.Add
 lLastRow = shtOrg.Cells(Rows.Count, 1).End(xlUp).Row

lRowLoop = 0

Do While lRowLoop * 5 < lLastRow
    shtOrg.Cells(lRowLoop * 5 + 1, 1).Resize(5).Copy
    shtDest.Cells(lRowLoop + 1, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True

    lRowLoop = lRowLoop + 1
Loop

With application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

Open in new window


Thomas
0
 
axessJoshAuthor Commented:
Where do i put that code?
0
 
nutschCommented:
Open your workbook and go to the visual basic editor (Alt + F11). Insert \ Module from the insert menu,

Copy the code in said module.

Run using F5 in the Visual Basic Editor or Alt+F8 in your workbook.

Thomas
0
 
axessJoshAuthor Commented:
very cool!

thanks.

I'll have to some cleaning to the data as I didn't realize there were some Canada addresses which offset the info, but I can see that will work once I normalize everything.
0
 
nutschCommented:
Glad to help.

Thomas
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now