Divide a long vertical string into shorter horizontal strings with VBA

[bilbon]

Hi
Can someone help me with VBA code to divide a long vertical string into horizontal strings that are 13 characters long and separated by comma. Each string shall begin with an E. The vertical string is in column A and may be different long, the result shall be written in column C.


/Bilbon

1		E,1,2,2,X,1,2,1,2,1,2,2,X,X
2		E,2,1,1,2,1,X,1,2,X,X,X,1,2
2		E,2,X,X,1,2,1,1,1,1,X,X,2,1
X
1
2
1
2
1
2
2
X
X
2
1
1
2
1
X
1
2
X
X
X
1
2
2
X
X
1
2
1
1
1
1
X
X
2
1

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub LongListToE13s()
  Dim R As Long, Data As Variant, Result As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  ReDim Result(1 To 1 + Int(UBound(Data) / 13), 1 To 1)
  For R = 1 To UBound(Data) Step 13
    Result((R + 12) / 13, 1) = "E," & Join(Application.Transpose(Application.Index(Data, Evaluate("ROW(" & R & ":" & R + 12 & ")"), 1)), ",")
  Next
  Range("C1").Resize(UBound(Result)) = Result
End Sub[/td]
[/tr]
[/table]

 

[bilbon]

Thanks Rick
Your code works perfectly
I am extremely grateful


/Bilbon