The Split/Join Functions

Splitting a string up into individual words

Now that we now that the string is made up of two or more words, i.e. it contains spaces, we can separate the string into its component words by using the Split function.  This function accepts two parameters - the name of the variable that holds the original string and the character that you want to split the string at.

Syntax
Variable Name = Split(String to Split, Location of Split)

Return Value
The function returns an array containing everything between the given characters
For example, if the string "a b c d" were passed through the function with the split parameter of a space, the result would be an array containing four elements - a,b,c and d

Usage
The Split function is useful for retrieving information from a long string.  An example of this is to split the original string up into its individual words.  Note, it is necessary to perform our check to make sure that spaces are present before we try and split the string at the point of the spaces because it would cause an error if no spaces were present:

Dim strSplitter() As String
If InStr(1, strString, " ") Then
    strSplitter = Split(strString, " ")
End If

Once the split function has been performed we can access the elements of the array named strSplitter in the normal way, the only consideration is the fact that we do not necessarily know how many elements the array contains because we do not know how many spaces there were in the original string.  It is necessary to calculate the amount of elements using the UBound command, this refers to the last element in the array.  So UBound(strSplitter) would return a number representing the last element in the array.  To traverse the array and output the words in reverse order:

Dim intCounter As Integer
For intCounter = UBound(strSplitter) To 0 Step -1
    Debug.Print strSplitter(intCounter)
Next

One real world usage of this may be that we are given pathnames and it is necessary to extract just the filenames from them, disregarding the paths.  Imagine the input:

C:\demo.txt, C:\Program Files\printer.csv, C:\My Documents\output.txt etc.
We can extract just the filenames using the Split function to dissect the string into its component parts, we now know that the last element in the array is the filename

Dim strSplitPath() As String
Dim strPathName As String
strPathName = "C:\Program Files\printer.csv"
strSplitPath = Split(strPathName, "\")

To refer to the last element of the array we can use the UBound keyword as described above:

Dim strFileName As String
strFileName = strSplitPath(UBound(strSplitPath))
Debug.Print strFileName

Joining the individual words together to form one string

We saw that the Split function can be used to chop a string up at specified points.  The Join function is the opposite of this, it accepts the name of a one dimensional array and joins all of the elements together

Syntax
Join(Source Array[, Delimiter])

Return Value
The function returns a string variable containing the reconstructed string.  The elements of the original array will be delimited by the specified character, however, the space (" ") character is used if a delimiter is not specified.  In the event of a zero-length string being specified as a delimiter, the array elements will be joined together with no seperators

Usage
The Join function is useful for constructing a string from substrings held in an array.  An example of its use may be to reverse the words in our original sentence

As before, the original string is divided into individual words

If InStr(1, strString, " ") Then
    strSplitter = Split(strString, " ")
End If

Dim strBackwards() As String
ReDim strBackwards(UBound(strSplitter))
Dim intBackCounter As Integer
intCounter = 0
For intBackCounter = UBound(strSplitter) To 0 Step -1
    strBackwards(intCounter) = strSplitter(intBackCounter)
    intCounter = intCounter + 1
Next

Dim strFinished As String
strFinished = Join(strBackwards, " ")