Separate / split string

[zuignap]

Hi people,

I would like to separate the numbers from the Comma , and give a
variable that number
i have the folliwing code:
-------------------------------------------------------
dim str as string
dim count as integer
str = "1,2,3,4,10"

count = len(str) 'result = 10

for i = 1 to count

n = (left(str,i))

debug.print "number: " & n
next i


I want to have the result like this in the direct window

result:
1
2
3
4
10

But there is something not good.. could you see what i do wrong?
Thanxs

 

[Tushar Mehta]

Just test your code with different values of i. What will left(str,i)
return for i=3? And, i=4 or i=5? How would you expect the software to
interpret those values?

If you are using Office 2000 or later, the programming platform is VB6. It
supports the Split() function. Look it up in XL/WD/PP VBA help.

If you are using XL97, you can simulate the effect of the Split() function
(with a comma for a delimiter) with the untested
Arr=Application.Evaluate("{" & str & "}")
where Arr is a variable declared as a variant.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Steve Rindsberg]

Try this:

Sub TestMe()

Dim Str As String
Dim sDelimiter As String

Str = "1,2,3,4,10"
sDelimiter = ","

' as long as there's a delimiter in the string
Do While InStr(Str, sDelimiter) > 0
' get the portion of the string to the left of the delimiter
Debug.Print Left$(Str, InStr(Str, sDelimiter) - 1)
' then continue with the portion of the string
' to the right of the delimiter
Str = Mid$(Str, InStr(Str, sDelimiter) + 1)
Loop
' and finally the bit to the right of the last comma:
Debug.Print Str

End Sub