I have a column of data, each row containing a set of 83 characters (e.g. 010203 344345 929348238482abcde33 4566) with no particular pattern that needs to be split into blocks of different lengths (e.g. 6,1,6,6,4,4,...4,1,1). What would be the best way to go about programmatically splitting these blocks? Would setting up a VBA list and looping through probably be the best bet?
I was assuming you wanted to have a different set of delineations for each row in your data set. The following code is to that end.
Create a column to the left of your data. Make sure there are no blanks. Select all the cells in that column and run the code. It will spit out the breaks to the right of your column with 83 characters. BTW, it will error if the sum of your breaks is greater than the length of characters (83 in this case).
Hope it helps.
Sub SplitManyTimes() Dim r As Range, rng As Range Dim arr As Variant Dim lLength As Long, lStart As Long, i As Long Set rng = Selection For Each r In rng lStart = 1 arr = Split(r, ",") For i = 1 To UBound(arr) + 1 lLength = Val(arr(i - 1)) r.Offset(0, i + 1) = Mid(r.Offset(, 1), lStart, lLength) lStart = lStart + lLength Next i Next r End Sub