Five Lines of Text to Five Columns

[hl2qs]

I have a number of contacts with their details on individual lines in Column A like this in Excel 2010, Windows 7:

Grounds Maintenance
Helper
Bob Smith
165 Cresap
Charlottesville, VA 22903
E bob@virginia.edu

Sports Management
Director
Jane Woodard
119 Stadium Road
Scottsville, VA 22605
434-924-5564
E jane@jmu.edu

I was hoping to convert the first five cells after each blank cell to five columns, the first cell after the blank one in column B, the second cell in column C, etc. Any ideas on how to do this in a couple steps?

Much obliged
Hamilton

 

[hurgadion]

Hi,
try using the macro of the form:

Sub Transposing()
Dim i&, ran As Range
    
For Each ran In Columns("A").SpecialCells(xlConstants).Areas
  i = i + 1
  Cells(i, 2).Resize(, ran.Rows.Count).Value = Application.Transpose(ran)
Next ran
End Sub

I find this Solution in this forum I don't remember who is the Author. Best regards.

 

[Rick Rothstein]

Assuming the data in Column A are constants (that is, not formulas), then this code should do what you want...

Sub ColumnsToRowsByAreas()
  Dim Ar As Range
  For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
    Ar(1).Offset(, 1).Resize(, Ar.Rows.Count) = WorksheetFunction.Transpose(Ar)
  Next
End Sub

 

[hl2qs]

Thanks hurgadion and Rick Rothstein, both codes worked like a charm.

Hamilton