Excel VBA MID()

[Zyel]

I've this long line of data

018:000000004000000297,004:W101,003:PAL,007:930.000,003:KAR,002:40,001:1,005:23.25,004:FERT,006:FM0001;018:000000004000000297,004:W101,002:MT,007:930.000
aaa:bbbbbbbbbbbbbbbbbb,aaa:bbbb,aaa:bbb,aaa:bbbbbbb

For the numbers in aaa, it is the length of data that is after the semi colon )

I'm trying to write a code where it will gives me this result

000000004000000297 | W101 | PAL | 930.000 | KAR | 40 | 1 | 23.25

I've been trying to use MID and LOOP statement for whole day... but not quite able to get it to work

 

[drsarao]

Split() is probably more useful here:

Sub Resplice()
iStr = "018:000000004000000297,004:W101,003:PAL,007:930.000,003:KAR,002:40,001:1,005:23.25,004:FERT,006:FM0001;018:0000000040000 00297,004:W101,002:MT,007:930.000 aaa: bbbbbbbbbbbbbbbbbb , aaa: bbbb , aaa: bbb , aaa: bbbbbbb"
s = Split(iStr, ",")
oStr = ""
For x = 0 To 7
    o = Split(s(x), ":")
    oStr = oStr + " | " & o(1)
Next x
MsgBox oStr
End Sub

Maybe you want the data split across cells rather than in a variable.
Modify the code accordingly.