How I can split the string with variable delimiter count:
s = "a1 b2 c d e"
into array:
arr(1) = "a1"
arr(2) = "b2"
arr(4) = "c"
arr(5) = "d"
arr(6) = "e"
The split-function does not give a desired result:
arr = Split(s, " ")
Thanks!
2 Answers
Use WorksheetFunction.Trim to remove leading and trailing spaces, as well as extra inner spaces.
Dim s As String '<~ don't use Str
s = "a b c d e"
s = WorksheetFunction.Trim(s)
2 Comments
Scott Craner
I am now waiting for the the, "This will not work as my delimiter is not actually a space but something else." comment.
malex
@scot-craner In my case is delimiter a space character :)
The pure VBA approach is to use a loop and the replace function
Public Function Dedup(ByVal ipSource As String, ByVal ipDedup As String) As String
Dim mySource As String
mySource = ipSource
Dim MyDedupDedup As String
MyDedupDedup = ipDedup & ipDedup
Do
DoEvents ' Always put a doevents in a Do loop
Dim myLen As Long
myLen = Len(mySource)
mySource = Replace(mySource, MyDedupDedup, ipDedup)
Loop Until myLen = Len(mySource)
Dedup = mySource
End Function
If you have leading or trailing characters you can use a more flexible trim function
Public Function Trimmer(ByVal ipString As String, Optional ByVal ipTrimChars As String = " ,;" & vbCrLf & vbTab) As String
Dim myString As String
myString = ipString
Dim myIndex As Long
For myIndex = 1 To 2
If VBA.Len(myString) = 0 Then Exit For
Do While VBA.InStr(ipTrimChars, VBA.Left$(myString, 1)) > 0
DoEvents ' Always put a do event statement in a do loop
myString = VBA.Mid$(myString, 2)
Loop
myString = VBA.StrReverse(myString)
Next
Trimmer = myString
End Function
1 Comment
malex
Thanks for your answer! In my case, the function "WorksheetFunction.Trim" is sufficient.
Stras a variable name... you're shadowing theStrfunction.