In this example you used a fixed mechanisme to split the info. In this
case it's important to find where the separator is located. In a
previous answer i mentioned a function which can be used when you
perform this in a worksheet. In a macro this is converted to a search
function.
I think it should be something like

move value to string
loop
start looking for separator
start=1
found .. move value from start=1 to pos found to tempvalue
start looking for separator
start=posfound + 1
found .. move value from start to pos found to tempvalue-2
etc
until end of string
write out values to columns  and move on to the next row

If I have any time left I'll see if I can give provide a working sample