The Join Function

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

After you've finished processing an array that's been split, you can use the Join function to concatenate the elements of the array together into a single string again. The Join function takes an array of strings and returns a concatenated string. By default it adds a space between each element of the string, but you can specify a different delimiter.

The following procedure uses the Split and Join functions together to trim extra space characters from a string. It splits the passed-in string into an array. Wherever there is more than one space within the string, the corresponding array element is a zero-length string. By finding and removing these zero-length string elements, you can remove the extra white space from the string.

To remove zero-length string elements from the array, the procedure must copy the non-zero-length string elements into a second array. The procedure then uses the Join function to concatenate the second array into a whole string.

Because the second array isn't created by the Split function, you must size it manually. It's easy to do, however — you can size it initially to be the same size as the first array, then resize it after you've copied in the non-zero-length string elements.

Function TrimSpace(strInput As String) As String
   ' This procedure trims extra space from any part of
   ' a string.

   Dim astrInput()     As String
   Dim astrText()      As String
   Dim strElement      As String
   Dim lngCount        As Long
   Dim lngIncr         As Long
   
   ' Split passed-in string.
   astrInput = Split(strInput)
   
   ' Resize second array to be same size.
   ReDim astrText(UBound(astrInput))
   
   ' Initialize counter variable for second array.
   lngIncr = LBound(astrInput)
   ' Loop through split array, looking for
   ' non-zero-length strings.
   For lngCount = LBound(astrInput) To UBound(astrInput)
      strElement = astrInput(lngCount)
      If Len(strElement) > 0 Then
         ' Store in second array.
         astrText(lngIncr) = strElement
         lngIncr = lngIncr + 1
      End If
   Next
   ' Resize new array.
   ReDim Preserve astrText(LBound(astrText) To lngIncr - 1)

   ' Join new array to return string.
   TrimSpace = Join(astrText)
End Function

To test the TrimSpace procedure, try calling it from the Immediate window with a string such as the following:

? TrimSpace("  This  is  a  test  ")

Tip   To see the elements in each array while the code is running, step through the procedure, and use the Locals window to view the values contained in each variable.

See Also

Working with Strings as Arrays | The Split Function | The Filter Function