how do i extract multiple data from a cell(string) whose length v.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gits - 19 Sep 2007 20:18 GMT

I need to extract all numbers into different columns (delimiter is ,). e.g.
1,325,16420,5,6,120,4643,2,8,-71,-68,-6 the length of numbers within
could vary.

Reply to this Message

Joel - 19 Sep 2007 20:54 GMT

Put you comma delimited string in cell A1. code will put numbers starting in
row 2 cell A2.

Sub splitstring()

MyString = Range("A1").Value
MyOff = 0
Do While InStr(MyString, ",") > 0
MyNum = Val(Left(MyString, InStr(MyString, ",") - 1))
MyString = Mid(MyString, InStr(MyString, ",") + 1)
Range("A2").Offset(0, MyOff).Value = MyNum
MyOff = MyOff + 1
Loop
MyNum = Val(MyString)
Range("A2").Offset(0, MyOff).Value = MyNum

End Sub

> I need to extract all numbers into different columns (delimiter is ,). e.g.
> 1,325,16420,5,6,120,4643,2,8,-71,-68,-6 the length of numbers within
> could vary.

Reply to this Message

Stefi - 20 Sep 2007 07:24 GMT

Data/Text to Columns/separated by comma

Regards,
Stefi

„Gits” ezt írta:

> I need to extract all numbers into different columns (delimiter is ,). e.g.
> 1,325,16420,5,6,120,4643,2,8,-71,-68,-6 the length of numbers within
> could vary.

Reply to this Message