Thread: function or vba to Split numbers from one cell

Hi Flebber, this macro should work for you. It assumes your xml data is in A1:A? on Sheet1. Adjust the code as needed to suit.

Option Explicit

Sub flebber()
Dim arr As Variant, i As Long
With Sheets("Sheet1")
arr = .Range("A1:A" & .UsedRange.Rows.Count)
For i = 1 To UBound(arr)
    .Range("B" & i & ":E" & i).Value = Split(arr(i, 1), "-")
Next i
End With
End Sub

Hope that helps!

Ok, where to begin.. this is going to get a bit technical, so hopefully you can follow along.

For i = 1 To UBound(arr)

That line will loop through the array named "arr" from the first entry to the last. In the line before that, I did a mass-move of data from A1:A?? into a two-dimensional horizontal array [arr(1,1) being the first value in that array, arr(2, 1) being the second, etc]. UBound stands for Upper Bound.

.Range("B" & i & ":E" & i).Value = Split(arr(i, 1), "-")

From the previous line, we're now looping through the array item by item starting at 1. So when i = 1, this line of code is essentially

.Range("B1:E1").Value = Split(arr(1, 1), "-")

arr(1, 1) refers to the first entry in the array, for example, "12-1-2-4" from cell A1.
Using the Split function splits that one entry into, in this case, four separate entries using "-" as the delimeter. So instead of having one array entry with "12-1-2-4", I now have four separate values in a temporary array, consisting of 12, 1, 2 and 4. Since I've referred to 4 individual cells to the left of the = sign (B1:E1), I am simply assigning those four cells the four values of the temporarily split array value.

Because the array is horizontal (it's created that way when you first load the values into the array even though it came from values going down a column), when you paste the array back to the worksheet you can paste it directly into a horizontal range containing the same number of cells as in the array (or temporary array in this case). If I had wanted to return the values to the worksheet in one column (e.g. B1:B4), I would need to Transpose the array (change it from horizontal to vertical) by using

.Range("B1:B4").Value = Application.Transpose(Split(arr(i, 1), "-"))

After that first line is done, we loop and i changes to 2. So we're then looking at the second value in the original array, as well as referring to the second row on the worksheet. Had the worksheet data started in a different row, like 20, we could have adjusted the code slightly to take that into account when adding the newly split data back to the sheet.

Hopefully that helps in some way!

you could have used text to columns with -as delimiter
either manually or something like just adapted from a recorded macro

Sub t2c()
    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    Other:=True, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1))
End Sub

you can still used text to columns
but fiddle it a bit
this macro copies col a to col b
text to columns on col a but skips all fields except last so dollar amount text remains in col a. then repeats on column b but skips the dollar amount

Sub t2c()
Application.ScreenUpdating = False
Range("b:b").Value = Range("A:A").Value
    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    Space:=True, Other:=True, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 9), Array(2, 9), Array(3, 9), Array(4, 9), Array(5, 1))
 Columns("b:b").TextToColumns Destination:=Range("b1"), DataType:=xlDelimited, _
    Space:=True, Other:=True, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 9))
   Application.ScreenUpdating = True
End Sub