Solved: Spliting a cell content based on two criteria

[kurtwagner]

Hello,
I need a macro that would split the content of a cell based on two separators at the same time: - and /

I could use text to columns, but it only allows me to use on separator or the other at a time.

Is there a way to use both conditions?

[Kenneth Hobs]

Provide examples.

[CodeNinja]

I don't think Split function can use 2 arguments... but you can write your own...

Function superSplit(sSplit As String, cArg1 As String, cArg2 As String) As Variant
Dim sArrayHolder() As String
Dim lChar As Long
Dim lLastSplit As Long


ReDim sArrayHolder(1 To 1)
lLastSplit = 1
For lChar = 1 To Len(sSplit)
If Mid(sSplit, lChar, Len(cArg1)) = cArg1 Then
If sArrayHolder(UBound(sArrayHolder)) <> "" Then ReDim Preserve sArrayHolder(1 To UBound(sArrayHolder) + 1)
sArrayHolder(UBound(sArrayHolder)) = Mid(sSplit, lLastSplit, lChar - lLastSplit)
lChar = lChar + Len(cArg1)
lLastSplit = lChar

End If
If Mid(sSplit, lChar, Len(cArg2)) = cArg2 Then
If sArrayHolder(UBound(sArrayHolder)) <> "" Then ReDim Preserve sArrayHolder(1 To UBound(sArrayHolder) + 1)
sArrayHolder(UBound(sArrayHolder)) = Mid(sSplit, lLastSplit, lChar - lLastSplit)
lChar = lChar + Len(cArg2)
lLastSplit = lChar

End If
Next lChar

If sArrayHolder(UBound(sArrayHolder)) <> "" Then ReDim Preserve sArrayHolder(1 To UBound(sArrayHolder) + 1)
sArrayHolder(UBound(sArrayHolder)) = Mid(sSplit, lLastSplit, lChar - lLastSplit)

superSplit = sArrayHolder

End Function

Sub test()
Dim s() As String
Dim i As Integer

s = superSplit("Split-Me/Now", "-", "/")
For i = LBound(s) To UBound(s)
MsgBox (s(i))
Next i
End Sub

The test sub runs the function which returns a split array based on - and /... you could also try using it in 1 line calling the argument you need... like this

MsgBox (superSplit("Split-Me/Now", "-", "/")(2))

The above will split given any two arguments... if that doesn't make sense to you, I can explain in further detail....

[GreenDR]

I dont have any other option than using for loop to iterate the string like below

Sub TEST()
Dim ln As Integer
Dim i As Integer
Dim str1 As String, str2 As String

ln = Len(Range("A1").Value)
For i = 1 To ln
If Mid(Range("A1").Value, i, 1) = "-" Or Mid(Range("A1").Value, i, 1) = "/" Then
str1 = Mid(Range("A1").Value, 1, i - 1)
str2 = Mid(Range("A1").Value, i + 1, Len(Range("A1").Value))
End If
Next

End Sub

[kurtwagner]

Thanks, they both work!

[Kenneth Hobs]

Sub Test_MSplit()
Dim c As Range, v As Variant
For Each c In Range("A4:A6")
With c
v = MSplit(.Value)
.Offset(0, 1).Resize(1, UBound(v) + 1).Value = v
End With
Next c
End Sub

Function MSplit(s As Variant) As Variant
MSplit = Split(Replace(s, "/", "-"), "-")
End Function