The split function isn't designed to remove characters from a string - so no
surprise it would be a disappointment. The Replace function does that. If
you want to put the data delimited in an array, a very popular requirement,
then the split function works quite well and is offered as a solution to a
wide variety of problems posted in this group.
As Dana pointed out, your RemoveDelimiters function just reinvents the
wheel. Replace would work much faster.
Am I missing something in the greatness of the SPLIT FUNCTION?
Probably an understanding of how to use it.
here is some sample code where using split came in very handy:
Sub FixData()
Dim rng As Range, cell As Range
Dim sStr As String, s As String
Dim v As Variant, i As Long
Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
For Each cell In rng
sStr = Replace(cell.Value, "<", "|(")
sStr = Replace(sStr, "", ")|")
If Left(sStr, 1) = "|" Then _
sStr = Right(sStr, Len(sStr) - 1)
If Right(sStr, 1) = "|" Then _
sStr = Left(sStr, Len(sStr) - 1)
sStr = Replace(sStr, "||", "|")
v = Split(sStr, "|")
s = ""
For i = LBound(v) To UBound(v)
s = s & Replace(Replace(v(i), _
"(", "<"), ")", "") & "<write:" _
& v(i) & ""
Next i
cell.Offset(0, 1).Value = s
Next cell
End Sub
--
Regards,
Tom Ogilvy
"davidm" wrote in
message ...
Thanks again, Ross. Your example is very clear and demonstrative. Never
in my wildest dream could I have thought that the SPLIT FUNCTION could
not be deployed on its own to remove delimiters. We are used to
functions (UDFs inclusive) being a quick-fix to deliver results. Not so
the SPLIT. If my observation is right, it will probably qualify as the
FUNCTION with the least utilty in Excel VBA. We could far more
readily remove the delimiters with a standard routine like:
Function RemoveDelimiters(txt as string, d as string)
For i = 1 to Len(txt)
If Mid(txt,i,1) < d then
k = k & Mid(txt,i,1)
Else
k = k & Chr(32)
End if
Next
RemoveDelimiters= k
End Function
Am I missing something in the greatness of the SPLIT FUNCTION?
David.
--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=480974