[AccessD] Turn a string into an array

[Stuart McLachlan]

" RemoveSpacesBeforeCommas" also appears to remove spaces after commas :)
But it doesn't handle leading spaces in the first value or trailing spaces in the last value
i.e. " 123 , 456 , I Love New York,   888 ,     55,  222,  7,    Bill Benson, Art Fuller    , 123  "
' 123'
'456'
'I Love New York'
'888'
'55'
'222'
'7'
'Bill Benson'
'Art Fuller'
'123  '

Also REGEX is generally rather slow :)

An alternative:
Public Sub RemoveLeadingAndTrainlingSpaces(ByRef str As String)
    While InStr(str, " ,")
        str = Replace(str, " ,", ",")
    Wend
    While InStr(str, ", ")
        str = Replace(str, ", ", ",")
    Wend
    str = Trim$(str)
End Sub


On 25 Aug 2021 at 19:35, Bill Benson wrote:

> >
> >
> > 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
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>