I found myself in a position where I needed to be able to take an arbitrary string and remove consecutive spaces in a string, replacing them with one space each.

Example:
“7122A05 N62A N656”
to: “7122A05 N62A N656”

This is a pretty simple problem so I didn’t really want to overthink it, but I wanted something fast.

I turned to CoPilot for a quick answer. It gave me a function that didn’t work, attempting to split the string into an array and then joining it back into a string. This doesn’t work because split creates empty elements for each void between the extra spaces, so joining that back together created the exact same string.

I asked for another way to do it and it created a function to loop over the string multiple times searching for two spaces and replacing them with a single space until it didn’t find two spaces any more:

Function RemoveExtraSpaces(ByVal text As String) As String
    Dim cleanedText As String
    cleanedText = Trim(text)
    
    Do While InStr(cleanedText, "  ") > 0
        cleanedText = Replace(cleanedText, "  ", " ")
    Loop
    
    RemoveExtraSpaces = cleanedText
End Function

' Example usage
Sub ExampleUsage()
    Dim line As String
    Dim cleanedLine As String
    
    line = "This   is  a   line    with multiple   spaces."
    cleanedLine = RemoveExtraSpaces(line)
    MsgBox cleanedLine  ' Output: "This is a line with multiple spaces."
End Sub

Actually, that’s not too bad. I was hoping for something more efficient though, so I asked for that.

It thought regular expressions would be more efficient and so gave me code utilizing the Scripting.RegExp object. That may be true, but I have concerns that the scripting DLL may someday be deprecated and so I am now leery of using it in new code.

I considered other ways to remove the spaces:

1. Loop character by character rebuilding the string, and skip multiple spaces
2. Use the Split / Join method but remove empty array elements before doing the Join
3. Build my own COM object using .NET or twinBASIC to handle regular expressions

But all of them seemed like they would either be slower or just plain take me longer to create the solution than was really necessary. So in the end, I went with the CoPilot generated function above.