[AccessD] Turn a string into an array

[Bill Benson]

I missed that failure at the last value. That said it seemed pretty obvious
no one would NOT want to remove both in this situation and I posted a
routine I *thought* was doing both. Just didn’t realize it wasn’t working
as intended.

Yes I did read somewhere that REGEX is slower than VBA string manipulations
lime your code. So since I don’t know a single thing about Regex I will
happily keep your solution handy rather than try to fix the other.

On Wed, Aug 25, 2021 at 8:49 PM Stuart McLachlan <stuart at lexacorp.com.pg>
wrote:

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