Ah, ok; let's talk about arrays for a bit.
An array is a sort of multiple variable. Instead of having one variable named (say) Price, you could make Price an array of 10 variables, each numbered Price(1) through Price(10). You could do it in a program like this:
Code:
Dim Price(1 to 10)
For jp = 1 to 10
Price(jp) = jp * 2
Next jp
That very simple-minded example creates 10 Prices, where Price(1)=2, Price(2)=4, Price(3)=6 and so on until Price(10)=20. If you want do, you can later put those prices into a spreadsheet:
Code:
Cells(MyRow, 1).Value = "Prices:"
For jp = 1 to 10
Cells(MyRow, jp + 1).Value = Price(jp)
Next jp
That's is just an example of how arrays work, you understand. But I mentioned arrays last time because there's a Split function that will conveniently take a character string and cut it up into pieces, each one in one member of an array. Suppose you have in A1 the following string, which I copied by hand (so it may have some mistakes) from your first example:
Code:
TY16#....NO AD....^\r\n250|499|15.51^\r\n | ^\r\n....WITH AD....^\r\n500|999|13.22^\r\n1000|2499|9.94^\r\n2500|4999|7.13^\r\n#0##
Now let's let VBA break that up into its component pieces:
Code:
data = Range("A1").Value
arr1 = Split(data, "#")
'arr1(0): "TY16"
'arr1(1): "....NO AD....^\r\n250|499|15.51^\r\n | ^\r\n....WITH AD....^\r\n500|999|13.22^\r\n1000|2499|9.94^\r\n2500|4999|7.13^\r\n"
'arr1(2): "0"
'arr1(3): ""
'arr1(4): ""
arr2 = Split(arr1(1),"^\r\n"
'arr2(0): "....NO AD...."
'arr2(1): "250|499|15.51"
'arr2(2): " | "
'arr2(3): "....WITH AD...."
'arr2(4): "500|999|13.22"
'arr2(5): "1000|2499|9.94"
'arr2(6): "2500|4999|7.13"
See how Split works? Next we would run through each of the pieces of arr2 and figure out which of those to further split using "|".
Does that help?
Two remaining questions from my post below:
1) When you created that first program and ran it, you pasted the code somewhere. Where did you put it?
2) Is my description of the data correct as far as it went?
I was going to ask a new one, too, but I just reread your original post and I'm thinking of something new. New post coming up.