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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.