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?

  • How is your data laid out initially? Provide a screenshot please. – Eric F Jan 15 '15 at 16:55
  • @EricF It is just in one column, each row containing an 83 character string. – Max Power Jan 15 '15 at 16:58
  • @MaxPower Is there a pattern to your lengths of blocks sequence? – Chrismas007 Jan 15 '15 at 16:59
  • and each cell looks like "6,1,6,6.." basically numbers delimitted by a comma? – Eric F Jan 15 '15 at 17:00
  • 1
    @MaxPower Do you want them split into separate columns? Because you could easily do that with a =MID() formula in each subsequent column then copy down (autofill) the formula. – Chrismas007 Jan 15 '15 at 17:07

This should do what you are looking for: Final

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.

| improve this answer | |

Max, I think I understand that you want your data delimitted by commas. Luckily Excel has a built in tool for that. See below:

enter image description here

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.

| improve this answer | |
  • His data isn't comma delineated. – Chrismas007 Jan 15 '15 at 17:18

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.