A basic question about strings

[Daniel Duta]

Hi all,
I know it sounds simple but I dont find a straightforward solution. I have a string that contains some empty spaces (one, two or even more) among other substring and I would like to replace all these spaces with a comma or any other separator. My string is as below:

Dim mySite As String
mySite = "BR0250 BR0014   TL1245    FR0298   BG2568 VN2563 ..."

The results should be something like :

mySite = "BR0250,BR0014,TL1245,FR0298,BG2568,VN2563"

Thank you.

[LaVolpe]

One solution (among a few I would imagine)
1. In a loop, replace all double spaces with single space
2. Then after the loop, replace all spaces with whatever delimiter you want

' Trim any leading/trailing spaces first if needed else can skip the next line
mySite = Trim$(mySite)
Do Until InStr(mySite, "  ") = 0 ' << 2 spaces in the quotes
   mySite = Replace(mySite, "  ", " ") ' << 2 spaces in 1st quotes, 1 space in 2nd quotes
Loop
mySite = Replace(mySite, " ", ",") ' << 1 space in quotes, replace "," with whatever delimiter you need

Note: above is thinking out loud and not terribly efficient. When replacing 2 spaces with 1 space, logic looks good. But if 3 spaces exist, those are processed twice: 1st time converting 2 of them to 1 space leaving 2 spaces; then 2nd time converting 2 spaces to 1 space

A more efficient routine would be more complex (of course). It would likely walk thru the string, 1 character at a time, looking for spaces, finding the next non-space character and sliding the string contents to the left to fill the extraneous spaces. That would not only be more efficient, but faster overall based on the length of the string. Why? Well, that method would use Mid$() to shift characters and not rebuild the string. Replace() rebuilds the string which is slower. If I had VB6 up & running right now, I'd probably whip something out (tested), but I don't want to post air-code with potential bugs

[JackB]

Private Function Spc2Commas(ByVal s As String) As String
Dim l, i As Integer
Dim Flg As Boolean
Dim c As String
Dim t As String

Flg = False
s = Trim$(s)
l = Len(s)
For i = 1 To l
    c = Mid$(s, i, 1)
    If c = " " Then
        If Not Flg Then
            t = t & ","
            Flg = True
        End If
    
    Else
        t = t & c
        If Flg Then Flg = False
    End If
Next i
Spc2Commas = t
End Function

Of course it can be shrinked

[Bonnie West]

A straightforward, albeit slow solution is via Regular Expressions:

Option Explicit

Private Sub Main()
    Dim mySite As String

    mySite = "BR0250 BR0014   TL1245    FR0298   BG2568 VN2563"

    With CreateObject("VBScript.RegExp") 'Or New RegExp if Microsoft VBScript Regular Expressions 5.5 is referenced
        .Global = True
        .Pattern = " +"

        mySite = .Replace(mySite, ",")
    End With

    Debug.Print """"; mySite; """"
End Sub

"BR0250,BR0014,TL1245,FR0298,BG2568,VN2563"

[jmsrickland]

nice except you forgot the Trim()

[Bonnie West]

Well, my example didn't need any trimming, but if Daniel thinks it's going to be necessary, then he can add it himself.

[dilettante]

A more efficient routine would be more complex (of course). It would likely walk thru the string, 1 character at a time, looking for spaces, finding the next non-space character and sliding the string contents to the left to fill the extraneous spaces. That would not only be more efficient, but faster overall based on the length of the string. Why? Well, that method would use Mid$() to shift characters and not rebuild the string. Replace() rebuilds the string which is slower.

Yes, things like Replace$() and the use of concatenation and loops extracting character-by-character can all add up to eat you alive if good performance is required.

Of course unless you are doing this for many thousands of Strings it is often best to keep things as short and readable as possible. The advantage is in avoiding subtle bugs and maintenance headaches down the road. All it takes is a minor change in requirements and "clever" code can fall apart badly, resulting in a lot of time spent reworking things and then validating them again. Data corruption is no fun, especially from subtle mistakes that go undiscovered until the original data is gone!


I won't claim this code is 100% perfect, though it seems to pass the edge-case tests I thought of. It should be pretty fast, can be sped up a little more by inlining the CharPtr() function and using a typelib for access to the shlwapi.dll functions. It is fairly flexible and ought to be easy enough to modify if needed.

'Obtains the length of the substring within a string that consists entirely
'of a group of characters.
Private Declare Function StrSpn Lib "shlwapi" Alias "StrSpnW" ( _
    ByVal pszStr As Long, _
    ByVal pszSet As Long) As Long

'Searches a string for the first occurrence of any of a group of characters.
Private Declare Function StrCSpn Lib "shlwapi" Alias "StrCSpnW" ( _
    ByVal pszStr As Long, _
    ByVal pszSet As Long) As Long

Private Function CharPtr(ByVal StringPtr As Long, ByVal Char As Long) As Long
    CharPtr = ((StringPtr Xor &H80000000) + (Char - 1) * 2) Xor &H80000000
End Function

Private Function DelimRunsToDelimiter( _
    ByRef Spaced As String, _
    Optional ByVal DelimSetIn As String = " ", _
    Optional ByVal DelimOut = ",") As String
    'Trims leading and trailing delimiters and trailing DelimOut.
    Dim SpacedPtr As Long
    Dim DelimsPtr As Long
    Dim DSize As Long  'Size of DelimOut.
    Dim Size As Long   'Size of Spaced in chars.
    Dim O As Long      'Next output char to be stored into result.
    Dim I As Long      'Next input char to be examined in Spaced.
    Dim L As Long      'Length of non-space run.

    SpacedPtr = StrPtr(Spaced)
    DelimsPtr = StrPtr(DelimSetIn)
    DSize = Len(DelimOut)
    Size = Len(Spaced)
    DelimRunsToDelimiter = Space$((Size + 1) * DSize) 'Maximum required buffer.
    O = 1
    For I = 1 To Size
        'Skip run of delimiters:
        I = I + StrSpn(CharPtr(SpacedPtr, I), DelimsPtr)
        If I <= Size Then
            'Measure non-space run:
            L = StrCSpn(CharPtr(SpacedPtr, I), DelimsPtr)
            'Accumulate non-space run then a comma:
            Mid$(DelimRunsToDelimiter, O, L) = Mid$(Spaced, I, L)
            Mid$(DelimRunsToDelimiter, O + L, DSize) = DelimOut
            O = O + L + DSize
            I = I + L 'Gets bumped 1 more by For/Next.
        End If
    Next
    If O > 1 Then
        'Trim excess buffer, including trailing comma:
        DelimRunsToDelimiter = Left$(DelimRunsToDelimiter, O - DSize - 1)
    Else
        DelimRunsToDelimiter = vbNullString
    End If
End Function

Test cases:

Private Sub Test( _
    ByVal Original As String, _
    Optional ByVal DelimSet As String = " ", _
    Optional ByVal DelimOut As String = ",")

    Dim Result As String

    Result = DelimRunsToDelimiter(Original, DelimSet, DelimOut)
    Print """"; Original; """"
    Print """"; Result; """"
    Print
End Sub

Private Sub Form_Load()
    AutoRedraw = True

    Test "BR0250 BR0014   TL1245    FR0298   BG2568 VN2563 ...", " /."
    Test "BR0250 BR0014   TL1245    FR0298./.BG2568 VN2563 ...", " /."
    Test "BR0250 BR0014   TL1245    FR0298   BG2568 VN2563    "
    Test " BR0250 BR0014   TL1245    FR0298   BG2568 VN2563 ", , " "
    Test "BR0250 BR0014 TL1245 FR0298 BG2568 VN2563"
    Test "A BR0250 C", , "; "
    Test "BR0250"
    Test ""
    Test "     "
End Sub

[Daniel Duta]

Thank you all for your reply. Usually I am looking for simple or elegant solutions and always I appreciate when a routine is based on the VB6 native functions/resources. I am aware of the VB6 limitations and in many situations we have to use dedicated API's or libraries but at least in cases like this I would expect something Basic. You know, some arithmetic problems require an arithmetic solving and not an algebraic one . Now, considering that my string has a certain specificity in the sense the number of spaces inside the string is variable while the substrings lenght is constant I could consider something like this :

Dim mySite As String, c As String, i As Long
    
mySite = "BR0250 BR0014   TL1245    FR0298   BG2568 VN2563"

'All substrings have the same lenght (6)
      mySite = Replace(mySite, " ", vbNullString)
For i = 1 To Len(mySite) Step 6
      c = c & Mid$(mySite, i, 6) & ","
Next i
      mySite = Left$(c, Len(c) - 1)

As I said, the solution above is a particular one but for the case when the substrings have a variable lenght - I suppose a more usual situation - I have in mind a different approach without considering the efficiency (speed) in any way but is pretty simple for sure

Dim mySite As String, arr() As String, i As Long

mySite = "BR0250 BR0014   TL1245    FR0298   BG2568 VN2563"

arr = Split(mySite, " ")
mySite = vbNullString

For i = 0 To UBound(arr)
    If arr(i) <> vbNullString Then
        mySite = mySite & arr(i) & ","
    End If
Next i
    mySite = Left$(mySite, Len(mySite) - 1)

[Schmidt]

Just another routine on the pile, which approaches the problem in a more generic fashion
(a function, which simply ignores "all withespace" and returns a WordList in a String-Array):

Function GetWordList(S As String) As String()
Dim B() As Byte, W() As String, i&, WChar&, WStart&, WLen&, WCount&
    B = S 'copy the WString-Content once into a ByteArray
    W = Split(vbNullString) 'ensure an empty String-Array-instance (0 to -1)
    
    For i = 0 To UBound(B) + 2 Step 2
      If i > UBound(B) Then WChar = 0 Else WChar = B(i) + B(i + 1) * 256&
      Select Case WChar
        Case 0 To 47, 58 To 64 '<- a simplified WhiteSpace-Definition
          If WLen Then
            ReDim Preserve W(WCount)
            W(WCount) = Mid$(S, WStart, WLen)
            WCount = WCount + 1: WLen = 0
          End If
        Case Else 'the current WChar is part of a word
          If WLen = 0 Then WStart = i \ 2 + 1
          WLen = WLen + 1
      End Select
    Next i
    
    GetWordList = W 'return the WordList-Array
End Function

Usage then possible in different ways (enumeration or re-joining):

Private Sub Form_Load()
  Const S$ = "a:123;  b->456,  c='789'"
  
  Dim Word 'simple enumerations are possible...
  For Each Word In GetWordList(S)
    Debug.Print Word
  Next
  
  '...or re-concatenations with any delimiters per VB-Join-Function
  Debug.Print Join(GetWordList(S), ",")
End Sub

The above then prints out (for the given Test-String S="a:123; b->456, c='789'"):

a
123
b
456
c
789

a,123,b,456,c,789

Olaf

[couttsj]

Here is another solution that takes advantage of the fact that all VB6 controls truncate strings at the first NULL character (Chr$(0)). It uses 2 text boxes and 1 list box that I made invisible. The text to be converted is displayed in the first text box, and clicking on the second text box adds each item to the list box. The items in the list box are then reassembled with a comma separator in the second text box.

Private Sub Form_Load()
    mySite = "BR0250 BR0014   TL1245    FR0298   BG2568 VN2563 "
    Text1.Text = mySite
End Sub

Private Sub Text2_Click()
    Dim N%
    mySite = Replace(mySite, " ", Chr$(0))
    Do Until Len(mySite) = 0
        List1.AddItem mySite
        N% = InStr(Len(List1.List(List1.ListCount - 1)), mySite, Chr$(0))
        If N% = 0 Then Exit Do
        Do Until Mid$(mySite, N%, 1) <> Chr$(0)
            N% = N% + 1
        Loop
        mySite = Mid$(mySite, N%)
        Debug.Print mySite
    Loop
    Text2.Text = List1.List(0)
    For N% = 1 To List1.ListCount - 1
        Text2.Text = Text2.Text & "," & List1.List(N%)
    Next N%
End Sub

J.A. Coutts

[Bonnie West]

... but is pretty simple for sure

The solutions in posts #2 & #4 both produce identical results to yours yet they have less lines of code.

... all VB6 controls truncate strings at the first NULL character (Chr$(0)).

That's because the underlying APIs uses C-style strings where the Null character serves as a string terminator.

[dilettante]

I can't imagine anyone using the slow and clunky Chr$(0) when vbNullChar is already predefined. Let alone using a ListBox for something like this... weird! At best a Collection would make more sense here.

I'd hate to use that approach to convert a 100,000 record file to CSV format though!