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?
3 Answers
This should do what you are looking for:
The formula in B2
is special because it starts you out:
=MID($A2, 1, B1)
but then C2
can be copied down the entire 18 remaining columns D2
->LastCol2
:
=MID($A2,SUM($B$1:B$1)+1, C$1)
Then copy the whole row down and the formulas should autofill correctly.
Max, I think I understand that you want your data delimitted by commas. Luckily Excel has a built in tool for that. See below:
Hopefully this is what you are after, otherwise try experimenting with the text to column tool since it is easier than writing a bunch of excel functions.
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.
Kyle
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
=MID()
formula in each subsequent column then copy down (autofill) the formula.