Split function in vba

[idnoidno]

D1.xlsm

I have not understood how to use the Split function, although I have looked at some of the information using the dictionary method, hope someone can tell me how to use it in my file.

[snb]

split only splits 'strings'.
There's no relation between Dictionary and split.

[mdmackillop]

This produces a nonsense result but at least it does something.
You need to ADD keys before you can do anything with them. You cannot SPLIT all keys in one action

Option Explicit

Sub t1()
Dim d As Object, i%, j%, ar, br, k


Set d = CreateObject("scripting.dictionary")
ar = [a2].CurrentRegion
For i = 1 To UBound(ar)
    If Not d.exists(ar(i, 1)) Then
        d.Add ar(i, 1), ar(i, 2)
    End If
Next i


For Each k In d.keys
br = Split(k, "-")
For i = 1 To d.Count
    For j = 0 To UBound(br)
        Cells(i + 1, j + 5) = br(j)
    Next j
Next i
Next k
[d2].Resize(d.Count) = Application.Transpose(d.items)
Set d = Nothing


End Sub

[SamT]

Split returns an array
Split has 2 parameters, the String, and the Separator, both enclosed in Double Quotes and with a comma between:

Split("String", "Separator")
Split("Long,String,separated,By,A,Separator", ","), in this case commas.

But can be Comma+Space (", "), or just a space (" ")
Can even be a Word ("LongWordStringWordSeparateWordByWordAWordOtherStringWordPeriod") or a Carriage Return (vbLF) or any ascii String (Chr(255)&Chr(125)&Chr(222))

Sub Test_Split()
Dim X As Variant
X = Split("Long,String,separated,By,A,Comma", ",")

MsgBox X(LBound(X))
MsgBox X(2)
MsgBox X(UBound(X))
End Sub

[idnoidno]

I did not expect someone to answer my question so soon.
Mr.mdmackillop
Thank you for telling me that
"You cannot SPLIT all keys in one action"
I do understand.
Thanks for all.

[snb]

Sub M_snb()
  sn = Sheets(1).Cells(2, 1).CurrentRegion
  
  With CreateObject("scripting.dictionary")
    For j = 1 To UBound(sn)
       sp = Array(sn(j, 2), Split(sn(j, 1), "-")(0), Split(sn(j, 1), "-")(1))
       If .exists(sn(j, 1)) Then sp(0) = .Item(sn(j, 1))(0) + sp(0)
       .Item(sn(j, 1)) = sp
    Next

    Sheets(1).Cells(2, 4).Resize(.Count, 3) = Application.Index(.items, 0, 0)
 End With
End Sub

BTW. You can split all keys in 1 action:

Sub M_snb()
  sn = Sheets(1).Cells(2, 1).CurrentRegion
  
  With CreateObject("scripting.dictionary")
    For j = 1 To UBound(sn)
       sp = Array(sn(j, 2), Split(sn(j, 1), "-")(0), Split(sn(j, 1), "-")(1))
       .Item(sn(j, 1)) = sp
    Next

    st=split(join(.keys,"-"),"-")
    Sheets(1).Cells(2, 4).Resize(ubound(st)+1) = Application.transpose(st)
 End With
End Sub

More on Dictionaries: http://www.snb-vba.eu/VBA_Dictionary_en.html

[mdmackillop]

BTW. You can split all keys in 1 action

Very clever.