Parse variable string (excel 2003)

[HUHandEH]

Hi all,

I have a string variable that I would like to parse much the same as "texttocolumns" I would like to know if there is a way to achieve a parse without using a hidden sheet.?

Thanks
Darryl

[rory]

What kind of parse do you want to do? If it's delimited, you can use the split function - e.g.
<pre>strWhatever = "test1,test2,test3"
varData = split(strwhatever, ",")
for n = lbound(vardata) to ubound(vardata)
..do whatever you want with the bits
next n
</pre>

[HansV]

Check out the Split function in the VBA help. It will split a variable according to the delimiter that you specify. The result is an array. Here is an example:
<code>
Sub Test()
Dim strVar As String
Dim arrParts() As String
Dim i As Integer
strVar = "this/that/other/such"
arrParts = Split(strVar, "/")
For i = LBound(arrParts) To UBound(arrParts)
Debug.Print i, arrParts(i)
Next i
End Sub
</code>
If you run this macro, the results will be displayed in the Immediate window (just as an illustration)

[HUHandEH]

Thanks guys,

both examples are great!

Darryl.