Function Split97(r, Optional u As String = ",")
Dim MidStr As Variant
Dim AnsStr As Variant
ReDim MidStr(0 To Len(r))
Dim i As Double, j As Double
Dim GetText, GetPoss
GetText = r
For i = 1 To Len(r)
GetPoss = InStr(GetText, u)
If GetPoss > 0 Then
MidStr(j) = Left(GetText, GetPoss - 1)
GetText = Mid(GetText, GetPoss + 1)
j = j + 1
Else
MidStr(j) = Mid(GetText, GetPoss + 1)
Exit For
End If
Next
ReDim AnsStr(j)
For i = 0 To j
AnsStr(i) = MidStr(i)
Next
Split97 = AnsStr
End Function
Sub test()
Dim t As String
t = "a~b~~c~dd"
Range("a7").Resize(2, 7) = Split97(t, "~")
End Sub