I would like to find an easy way to *split* a string by a delimiter.
For example, if I had this string:

"Rush,Enchant,Symphony X"

I'd like to be able to turn it into an array like this:

("Rush", "Enchant", "Symphony X")

I've seen some people use 'Split()' on this forum. For example, in
mikeburg's post:

-------
AEmployeeLastName = Split(Cell.Offset(0, -4), "
")(UBound(Split(Cell.Offset(0, -4), " ")))
-------

But this doesn't work on my Mac. I'm guessing it only works on a PC?
(By the way, is there a web sight that tells the differences between
VBA on a Mac versus a PC?)

I next tried coming up with a simple subroutine to split this string:

-------
Sub test3()
Dim bandList As String, theArray(0 To 2), theText As String

bandList = "Rush,Enchant,Symphony X"
n = 0
theText = ""

For x = 1 To Len(bandList)
If Mid(bandList, x, 1) = "," Then
theArray(n) = theText
n = n + 1
theText = ""
Else
theText = theText & Mid(bandList, x, 1)
End If
Next x

theArray(n) = theText

MsgBox theArray(0) & vbLf & theArray(1) & vbLf & theArray(2)
End Sub
-------

I'm thinking surely there's an easier way to do this? Is there
something else on the Mac similar to Split()?

While doing this, I stumbled on another question. I know that I can
reDim the array, but what if I don't know how many elements will be
placed in it? For example, let's say I have a cell that contains this
text:

Rush,Enchant,Symphony X,Journey,Iron Maiden

With my above subroutine (test3) I don't know how many items will be
placed in the array. Yes, I will end up with 5 items in there, but
I'll only know that once the code is done looping through each letter
in the string. So I really can't reDim the array unless I want to
reDim it every time through the loop. I could of course set the array
to (0 To 100) which would probably be safe, but is there a more
accurate way to do this? It would be nice not having to set the array
and then just using whatever amount of it I needed.

Thanks for looking at my questions.

Jay


--
jayy
------------------------------------------------------------------------
jayy's Profile: http://www.excelforum.com/member.php...o&userid=33975
View this thread: http://www.excelforum.com/showthread...hreadid=539947