Splitting Data in Excel Based on Character Location

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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