VBA Split Function

The VBA Split function is a string function that is used to divide a text string into an array of substrings. These substrings are separated by a specified delimiter, such as a comma or space. The Split function is commonly used in VBA to manipulate and extract data from strings.

VBA Split Function – Purpose, Syntax and Arguments

Purpose

The purpose of the Split function is to divide a string into smaller parts, making it easier to work with and manipulate specific pieces of data within the string. This can be useful for tasks such as parsing data from a file or extracting information from a longer string. The Split function is essential for handling and manipulating data in VBA, making it a crucial tool for programmers.

Syntax

The syntax for the Split function is as follows:

Split(expression[, delimiter[, limit[, compare]]])

Where:

• Expression: The text string that will be split into an array.
• Delimiter (optional): The character or characters that will be used to split the string. If this argument is not specified, the default delimiter is a space.
• Limit (optional): The maximum number of substrings that will be created in the array. If this argument is not specified, the default value is -1, which means all possible substrings will be created.
• Compare (optional): The type of string comparison that will be used. This argument can take the values 0, 1, or 2, which correspond to different comparison methods. If this argument is not specified, the default value is 0, which performs a binary comparison.

Example

Let’s take a look at an example of how the Split function can be used in VBA code. Suppose we have a sentence stored in a variable called ‘str’ like this:

str = "Hello, World! This is a test."

If we want to extract the words from this sentence, we can use the Split function. In the following code, we will use a space as the delimiter, so each word will be split into a separate element in the array:

Dim words() As String
words = Split(str, " ")

The resulting array, ‘words’, will contain the following elements:

words(0): Hello,
words(1): World!
words(2): This
words(3): is
words(4): a
words(5): test.

We can also use the Split function to extract specific pieces of data from a string. For example, if we have a string containing a person’s full name, like this:

fullName = "John Doe"

We can use the Split function with a space as the delimiter to split the string into two elements, the first name and the last name:

Dim firstName As String
Dim lastName As String
firstName = Split(fullName, " ")(0)
lastName = Split(fullName, " ")(1)

The variable ‘firstName’ will now contain “John” and the variable ‘lastName’ will contain “Doe”.

Remarks and Important Notes

• The Split function is often used in combination with the ‘Join’ function, which performs the opposite operation and combines an array of strings into a single string.
• If the delimiter is not found in the string, the entire string will be returned as the first element in the array.
• The Split function is case-sensitive, meaning that it will differentiate between uppercase and lowercase letters when splitting the string.
• If the delimiter is an empty string, each character in the string will be split into a separate element in the array.
• If the delimiter is a multi-character string, it must be an exact match to the part of the string that it is looking for in order to split the string.
• The Split function can also be used with other data types, such as dates or numbers, but the resulting array will still be an array of strings.

Understanding VBA Split Function with Examples

Example 1: Splitting a String into an Array

The Split function in VBA is used to split a string into an array based on a specified delimiter. This can be useful for manipulating or analyzing data contained within a string.

Sub SplitString()
    Dim data As String
    Dim dataArray() As String
    data = "apple,banana,orange,grape"
    dataArray = Split(data, ",") ' split the string using comma as delimiter
    MsgBox "First fruit: " & dataArray(0) ' displays "First fruit: apple"
    MsgBox "Number of fruits: " & UBound(dataArray) + 1 ' displays "Number of fruits: 4"
End Sub

Explanation: In this example, we have a string called “data” that contains a list of fruits separated by commas. We use the Split function to split this string into an array called “dataArray”. The first argument of the Split function is the string that we want to split, while the second argument is the delimiter that we want to use. In this case, we specify a comma as the delimiter. As a result, the string is split into an array with four elements, where each element contains a different fruit. We can then access each element of the array using its index. In this example, we display the first fruit in the array (“apple”) and also the total number of fruits in the array (4).

Example 2: Limiting the Number of Array Elements

The Split function also allows us to specify the maximum number of elements that we want in the resulting array. This can be useful if we only need a certain number of elements from a larger string.

Sub SplitString()
    Dim data As String
    Dim dataArray() As String
    data = "1,2,3,4,5,6"
    dataArray = Split(data, ",", 3) ' split the string using comma as delimiter and limit to 3 elements
    MsgBox "First three numbers: " & dataArray(0) & ", " & dataArray(1) & ", " & dataArray(2)
End Sub

Explanation: In this example, we have a string called “data” that contains a list of numbers separated by commas. We use the Split function with a delimiter of comma and specify the maximum number of elements as 3. This means that the resulting array will only contain the first three numbers from the string. The first three elements of the resulting array are then displayed in a MsgBox, showing that the Split function has successfully limited the number of elements in the array.

Example 3: Splitting a String into an Array of Characters

The Split function can also be used to split a string into an array of individual characters. This can be useful for performing specific operations on each character in the string.

Sub SplitString()
    Dim data As String
    Dim characterArray() As String
    data = "Hello World"
    characterArray = Split(data, "") ' split the string into individual characters
    For Each character In characterArray
        MsgBox character ' displays each character in the string
    Next character
End Sub

Explanation: In this example, we have a string called “data” that contains the phrase “Hello World”. By leaving the delimiter argument blank in the Split function, we can split the string into an array of individual characters. We then use a For Each loop to go through each element in the array and display it in a MsgBox. This example shows how the Split function can be used to perform operations on individual characters in a string.

Example 4: Splitting a String into an Array with a Custom Delimiter

In addition to using a single character as a delimiter, the Split function also allows us to specify a custom delimiter consisting of multiple characters.

Sub SplitString()
    Dim data As String
    Dim dataArray() As String
    data = "apple and banana- and orange and -grape"
    dataArray = Split(data, " and ") ' split the string using the custom delimiter " and "
    MsgBox "Second fruit: " & dataArray(1) ' displays "Second fruit: banana"
    MsgBox "Number of fruits: " & UBound(dataArray) + 1 ' displays "Number of fruits: 4"
End Sub

Explanation: In this example, we have a string called “data” that contains a list of fruits separated by the string ” and “. We use this string as a custom delimiter in the Split function, and the resulting array will have separate elements for each fruit. By accessing the second element in the array, we can display the second fruit (“banana”). This example shows how the Split function can be used with custom delimiters for more complex string manipulation.

Example 5: Splitting a String with a Dynamic Delimiter

The delimiter argument in the Split function can also be a dynamic value instead of a constant. This means that we can specify a variable as the delimiter, making the Split function more flexible.

Sub SplitString()
    Dim data As String
    Dim delimiter As String
    Dim dataArray() As String
    data = "apple,banana,orange,grape"
    delimiter = ","
    dataArray = Split(data, delimiter)
    MsgBox "First fruit: " & dataArray(0) ' displays "First fruit: apple"
    delimiter = "|"
    dataArray = Split(data, delimiter)
    MsgBox "Second fruit: " & dataArray(1) ' displays "Second fruit: banana"
End Sub

Explanation: In this example, we first declare a string variable called “delimiter”. We then use this variable as the delimiter in the Split function, which allows us to change the delimiter value at any time. This can be useful if we want to split a string using different delimiters in different scenarios. In the first case, the string is split using a comma as the delimiter, while in the second case, the string is split using a pipe symbol. By changing the value of the “delimiter” variable, we can control how the string is split and manipulate the resulting array accordingly.

In conclusion, the Split function in VBA is a powerful tool for manipulating strings and data. It allows us to split a string into an array based on a specified delimiter, including custom and dynamic delimiters. With these versatile capabilities, the Split function is a valuable addition to any VBA programmer’s toolkit.