VBA: Split string between two words to extract sub string

[FryGirl]

I have a sting and the code below works find to return the name. I just don't understand what the numbers in the parenthesis are doing?

DEMOGRAPHICS NAME: Donald J. Duck BANK: 451CS

Sub trythis()
    Dim MyText As String
    MyText = Trim(Split(Split(Range("A1"), "NAME: ")(1), "BANK:")(0))
    Range("A5").Value = MyText
End Sub

 

[Rick Rothstein]

I have a sting and the code below works find to return the name. I just don't understand what the numbers in the parenthesis are doing?

DEMOGRAPHICS NAME: Donald J. Duck BANK: 451CS

Sub trythis()
    Dim MyText As String
    MyText = Trim(Split(Split(Range("A1"), "NAME: ")(1), "BANK:")(0))
    Range("A5").Value = MyText
End Sub

The Split function creates an array... instead of assigning it to a variable and then pulling the elements out from it, the coder chose to pull the elements out of the array create by Split directly. For example, and keeping in mind the Split function's elements always start at 0...

MsgBox Split("One,Two,Three", ",")(0)

displays "One"

MsgBox Split("One,Two,Three", ",")(1)

displays "Two"

MsgBox Split("One,Two,Three", ",")(2)

displays "Three".

 

[rlv01]

With some intermediate variables the code becomes more understandable. Use the debugger to single step through the code to see what it does.

Sub trythis2()
    Dim MyText As String
    Dim S1 As String
    Dim S2 As String
    Dim S3 As String
    Dim S4 As String
    Dim S5 As String
    Dim SA1 As Variant
    Dim SA2 As Variant

    S1 = Range("A1").Text                             'String

    SA1 = Split(S1, "NAME: ")                         'Split S1 into an array of strings

    S2 = SA1(1)                                       'Element 1 of SA1, an array of strings

    SA2 = Split(S2, "BANK:")                          'Split S2 into an array of strings

    S3 = SA2(0)                                       'Element 0 of SA2, an array of strings

    S4 = Trim(S3)

    MyText = S4

    Range("A5").Value = MyText
End Sub

 

[Macropod]

Following on from Rick's post, the inner split, i.e.:
Split(Range("A1"), "NAME: ")(1)
splits the content of A1 at 'NAME:'. The first element of the array thus generated contains 'DEMOGRAPHICS ', whilst the second element contains ' Donald J. Duck BANK: 451CS'. Since the array is 0-based, the index of the first element of the array is 0 and the index of the second element of the array is 1; hence the (1) to return the second element. The formula:
Split(Split(Range("A1"), "NAME: ")(1), "BANK:")(0)
is now equivalent to:
Split(" Donald J. Duck BANK: 451CS", "BANK:")(0)
and, similar to the inner split, the first element of the array thus generated contains ' Donald J. Duck ', which is what the (0) returns.
The all-enclosing TRIM() simply strips off any surrounding spaces, leaving you with 'Donald J. Duck'.

 

[FryGirl]

Thanks to all for sharing this information. It definitely helps.