extract the data from a string of data

[garyc]

can we get the data from a string of data with marks to separate the useful items

For exampe,

apple, banana, orange, melon,......

-->

turn into a spreadsheet in a column

Cell
A1: apple
A2: banana
A3: orange
A4: melon

Can we do this with VBA in excel ????????????????????
I just know I can use len() and left() and right() and find()
but I just can do the above process when the string just contain 2 items.

Thanks!

[JimmyTheHand]

[vba]Range("A1:A4") = Application.Transpose(Split("apple,banana,orange,melon", ","))[/vba]

[garyc]

Thanks for your Answer !JummyTheHand!
However, I still have some problem. What if the string is really long, and you don't know how many items inside the string. How to tackle this problem? How to set the range at the left hand side.

(By the way, This forum is really awesome. This is my first day here. The problem posted are quickly solved. I guess this forum is full of smart guys who good at VBA!)

[Bob Phillips]

[vba]

Dim ary As Variant

ary = Split("apple,banana,orange,melon", ",")

Range("A1").Resize(UBound(ary, 1) - LBound(ary, 1) + 1) = Application.Transpose(ary)
[/vba]

[garyc]

Thanks xld !
This forum is really helpful !!!