Parse Functions‎ > ‎

PART 1 - Data separated by blank cells

PROBLEM:

"I have a single column of data that contains multiple data sets of varying lengths. Each data set is separated by a space.
I need each group of data in a column of its own."

EXAMPLE
BEFORE

 A B C D 1 0.35 2 0.33 3 0.21 4 5 0.2 6 0.16 7 1.61 8 1.2 9 0.08 10 0.51 11 12 0.37 13 0.56 14 0.7 15 0.77 16 0.85 17 0.67 18 19 0.77 20 0.85 21 0.67

AFTER

 A B C D E 1 0.35 0.2 0.37 0.77 2 0.33 0.16 0.56 0.85 3 0.21 1.61 0.7 0.67 4 1.2 0.77 5 0.08 0.85 6 0.51 0.67 7 8 9

SPECIFICATIONS:
1. Data all in column A
2. Data is flat values, not formulas
3. Data is separated by at least one blank cell per group

CODE

Sub ColumnToColumns()
'Jerry Beaucaire, 2/7/2012
'Groups of data in column A separated by blank cell(s)
'are moved into individual columns
Dim i As Long, RNG As Range
Application.ScreenUpdating = False
Set RNG = Columns("A:A").SpecialCells(xlCellTypeConstants)
For i = 2 To RNG.Areas.Count
RNG.Areas(i).Cut Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
Next i
Set RNG = Nothing
Application.ScreenUpdating = True
End Sub

PART 2 - Contiguous Data

PROBLEM:

"I have a single column of data that needs to be parsed out to multiple columns for ease of examination.  I need a macro to split the one column into multiple columns by the number of cells I designate each time it is run."

EXAMPLE
BEFORE

 A B C D 1 John 2 Sam 3 Phil 4 Bob 5 Doug 6 Porter 7 Reynold 8 Phil 9 Eric 10 Paul 11 Randy 12 Thomas 13 Gerald 14 Stan 15 Scott 16 Tom 17 Michael 18 Roger 19 Josh 20 Matt 21 Gary

AFTER

 A B C D 1 John John Phil Scott 2 Sam Sam Eric Tom 3 Phil Phil Paul Michael 4 Bob Bob Randy Roger 5 Doug Doug Thomas Josh 6 Porter Porter Gerald Matt 7 Reynold Reynold Stan Gary 8 Phil 9 Eric 10 Paul 11 Randy 12 Thomas 13 Gerald 14 Stan 15 Scott 16 Tom 17 Michael 18 Roger 19 Josh 20 Matt 21 Gary
SPECIFICATIONS:
1. Data all in column A
2. Data is flat values, not formulas
3. Macro will ask how many cells to split out to each new column

CODE

Sub ColumnToColumns()
'Jerry Beaucaire, 5/16/2012
'Take a column of values and split into multiple columns by X cells
Dim X As Long, RNG As Range, Grp As Long

X = Application.InputBox("How many cells in each group?", "Cells per group", 48, Type:=1)
If X = 0 Then Exit Sub

Set RNG = Range("A1", Range("A" & Rows.Count).End(xlUp))

For Grp = 1 To RNG.Rows.Count Step X
RNG.Cells(Grp).Resize(X).Copy Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
Next Grp

End Sub

Nothing says "thanks" like a steak dinner!