[AccessD] Turn a string into an array
bensonforums at gmail.com
Thu Aug 26 09:43:22 CDT 2021
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
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>
> " 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'
> 'I Love New York'
> '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, " ,", ",")
> While InStr(str, ", ")
> str = Replace(str, ", ", ",")
> 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
> Website: http://www.databaseadvisors.com
More information about the AccessD