Home » Forums » Open Source Learning Center » Question and Answer

How to break a string into substrings and save substrings into array?

Submitted by jangirpa on 9 January, 2013 - 15:47

I am having a situation while writing a function in excel VBA.

a variable is having a long string as below:

test = "jhon~Pass, kareem~pass, lacy~fail,Jenat~pass, milo~fail"

I want to store name in one array and the corresponding result values in another array.

Please help me, how it can be done?

‹ please help me with creating a drop down list that will activate a subroutine how to change zero to dash? ›
»
Vishesh's picture

String into substrings into array

Submitted by Vishesh on 10 January, 2013 - 15:36.

Sub DoIt2()
Dim test As String
Dim arr
Dim x As Integer

test = "jhon~Pass, kareem~pass, lacy~fail,Jenat~pass, milo~fail"

arr = Split(test, ",")
ReDim arrName(LBound(arr) To UBound(arr))
ReDim arrPassFail(LBound(arr) To UBound(arr))

For x = LBound(arr) To UBound(arr)
arrName(x) = Split(arr(x), "~")(0) 'array of name
arrPassFail(x) = Split(arr(x), "~")(1) 'array of pass/fail
Next x
End Sub

»
Nick's picture

Sub DoIt() test = "jhon~Pass,

Submitted by Nick on 9 January, 2013 - 16:25.
Sub DoIt()
test = "jhon~Pass, kareem~pass, lacy~fail,Jenat~pass, milo~fail"
test = Split(test, ",")
 
myNames = test
For Each theName In myNames
    theName = Left(theName, InStr(1, theName, "~") - 1)
Next
 
myPassFail = test
For Each thePassFail In myPassFail
    thePassFail = Right(thePassFail, Len(thePassFail) - InStr(1, thePassFail, "~"))
Next
 
End Sub
»