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...

[vba]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[/vba]

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

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

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

[VBA]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[/VBA]

[kurtwagner]

Thanks, they both work!

[Kenneth Hobs]

[VBA]
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
[/VBA]