[AccessD] Turn a string into an array

[Bill Benson]

>
>
> Arthur I noticed some spaces before or after your commas you might like
> this strategy which trims spaces but not between words- which can't be done
> easily with VBA without looping. It is slower than string manipulations
> though.


Results in immediate window:

call Test
'123'
'456'
'I Love New York'
'888'
'55'
'222'
'7'
'Bill Benson'
'Art Fuller'
'123'


Sub Test(Optional strListOfItems As String = _
    "123 , 456 , I Love New York,   888 ,     55,  222,  7,    Bill Benson,
    Art Fuller    , 123")
Dim vList           As Variant
Dim iItem           As Long
    RemoveSpacesBeforeCommas strListOfItems
    vList = Split(strListOfItems, Chr(44))
    For iItem = LBound(vList) To UBound(vList)
        Debug.Print "'" & vList(iItem) & "'"
    Next
End Sub

Public Sub RemoveSpacesBeforeCommas(ByRef str As String)
Dim objREGEX As Object
Set objREGEX = CreateObject("vbscript.RegExp")
With objREGEX
    .Global = True
    .Pattern = " *, *"
    str = objREGEX.Replace(str, ",")
End With
End Sub