Converting a string to an array

I have a string read from a text file containing data in the format of: name,status,userfolder,password
For example: Jack,active,user1,beanstalk

Now I need to convert this string to an array so I can use this data. I tried using InStr, Left$ and Right$ searching for the "," on only a string but it got too messy after a while.

The thing is that the string reads "name,status,userfolder,password" and an array will not accept the quote marks. I wonder if there is an easy way to convert it (or just lose the quotes)?
Who is Participating?

Improve company productivity with a Business Account.Sign Up

watyConnect With a Mentor Commented:
Use the following function :

Public Function GetTokens(sToParse As String, sToken As String) As Variant
   ' *** Returns an array to tokenized values
   ' ***  Ex:  GetTokens("Jack,active,user1,beanstalk", ",") = ({ "Jack", "active", "user1", "beanstalk" })

   Dim nTokenLen       As Integer
   Dim nTokenCnt       As Integer
   Dim nOffset         As Long
   Dim nPrevOffset     As Long
   Dim aTokens()       As String

   nTokenLen = Len(sToken)
   nOffset = InStr(sToParse, sToken)

   Do While nOffset > 0
      ReDim Preserve aTokens(nTokenCnt)
      If nOffset - nPrevOffset > 1 Then
         aTokens(nTokenCnt) = Mid$(sToParse, nPrevOffset + 1, nOffset - 1 - nPrevOffset)
         aTokens(nTokenCnt) = ""
      End If

      nPrevOffset = nOffset
      nOffset = InStr(nOffset + nTokenLen, sToParse, sToken)
      nTokenCnt = nTokenCnt + 1

   ReDim Preserve aTokens(nTokenCnt)
   aTokens(nTokenCnt) = Mid$(sToParse, nPrevOffset + 1)
   GetTokens = CVar(aTokens)

End Function

The following example uses the Split function to return an array from a string.
Dim MyArray
MyArray = Split("name,status,userfolder,password", ",")

' The "," is used as a delimiter(break-up character)
' MyArray(0) contains "name".
' MyArray(1) contains "status".
' MyArray(2) contains "userfolder".
' MyArray(3) contains "password".

I hope this is what you wanted.  /slobstar
Private Sub Split(ByVal sString As String, x() As String, n As Long)

'sString your string "name,status,userfolder,password"
'x is your target array
'n is the number of elements returned

    sString = Mid(sString, 2)  'remove leading quotes
    sString = Left(sString, Len(sString) - 1) 'remove final quote
    If InStr(sString, ",") = 0 Then
        n = 1
        x(n) = sString
        While InStr(sString, ",") <> 0
            n = n + 1
            x(n) = Left(sString, InStr(sString, ",") - 1)
            sString = Mid(sString, InStr(sString, ",") + 1)
        If sString <> "" Then
            n = n + 1
            x(n) = sString
        End If
    End If
End Sub

'sample call
Private Sub Command1_Click()

    Dim z(5) As String
    Dim n As Long

    Call Split("""name,status,userfolder,password""", z, n)
    For c = 1 To 5
        MsgBox z(c)
    MsgBox n

End Sub

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.