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.

Please Login or Register  to view this content.

Hope that helps!

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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.