PDA

View Full Version : Solved: Spliting a cell content based on two criteria



kurtwagner
12-11-2012, 09:40 AM
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
12-11-2012, 09:59 AM
Provide examples.

CodeNinja
12-11-2012, 10:09 AM
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
12-11-2012, 10:26 AM
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
12-11-2012, 10:31 AM
Thanks, they both work!

Kenneth Hobs
12-11-2012, 10:48 AM
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