VBA Split Function
When working with VBA Excel, the user may often require splitting a string into substrings based on a given delimiter. For example, if given a location, you can take advantage of the VBA Split function to get different parts of the location separated by a space (in this case, the space would be the delimiter).
This tutorial will run you through the definition, syntax, parameters, return type, and different examples explaining how to write a macro code using the VBA Split function.
What is Split Function?
“The SPLIT String function is used to split a string into substrings based on the supplied delimiter. It returns a zero-based, one-dimensional array holding the parts of substrings.”
The VBA Split function is an inbuilt function categorized under String/Text Functions. This function is coded in VBA macro in the Microsoft Visual Basic Editor. However, you can also use Split as an Excel function in Excel spreadsheets.
Syntax
Parameter
- Expression (required): This parameter represents the input string that you want to split based on the delimiter.
- Delimiter (optional): This parameter represents the delimiter used to split the string. It usually involves the ‘Expression’ argument. For instance, you have a string “Hello,World,This,is,my,program” here, a comma is a delimiter that can be used in the Split function to split the string into different substrings. If you have not passed any value for this argument, the space character is taken by default.
- Limit (optional): This parameter specifies the total number of substrings that the user want to return. For instance, if you only want to extract the initial 3 substrings from the string, you will pass 3 in this argument.
If you have not passed any value for this argument, -1 is taken by default, which returns all the substrings. - Compare (optional): When evaluating the substrings, this parameter returns the type of comparison the user wishes to use in the SPLIT function. Below given are a few compare options:
- When Compare is 0: 0 represents the Binary comparison. It is helpful when your delimiter is a text string (for example XYZ), this would be case-sensitive. ‘XYZ’ will be different from small case ‘xyx’.
- When Compare is 1: 1 represents the Text comparison. It is helpful if your delimiter is a text string (for example XYZ), even if you have ‘xyz’ in the ‘Expression’ string, it would be considered as a delimiter.
Return
The VBA Split function returns a zero-based, one-dimensional array holding the parts of substrings.
Examples
Split Program 1: Split the String using the default space delimiter.
String = Welcome to the world of VBA programming
VBA Split is an inbuilt function that quickly helps to split a string into substrings based on the supplied delimiter. Below given are the steps to write a VBA macro to split your String using the Split function:
Step 1: Go to the VBA developer tab either by clicking the shortcut keywords Alt +F11 or click on developer window -> visual basic editor.
Step 2: The VB Editor will appear. The next step is to create a module. Right-clicking on the VBA Project-> Click on Insert-> Click on Module.
Step 3: The Moule window will be inserted. Introduce the macro name followed by the declaration of the variable. Next, we will introduce a method named as Result().
Refer to the below given macro code:
Step 4: We will call the Split function, pass the string ‘Textstr’ in it and store the value in the result method.
Refer to the below given macro code:
Output
Run the code by pressing the F5 key and fetch the output. As a result Split function splits the string ‘textstr’ and stores each substring to the Result array.
At backend we will have following substrings:
- Result(0) stores the substring “Welcome”
- Result(1) stores the substring “To”
- Result(2) stores the substring “World”
- Result(3) stores the substring “of”
- Result(4) stores the substring “VBA”
- Result(5) stores the substring “programming.”
NOTE: In this program, the delimiter argument is omitted so it takes the space character as the default delimiter.
Split Example 2 – Count the Number of Words present in a string
The VBA SPLIT function can also be used tactfully to find the total words present in a sentence. The program will be same as above, the only trick here is to count the number of words assigned in the Result array after splitting the string.
Below given are the steps to write a VBA macro to get the count of words using the Split function:
Step 1: Go to the VBA developer tab either by clicking the shortcut keywords Alt +F11 or click on developer window -> visual basic editor.
Step 2: The VB Editor will appear. The next step is to create a module. Right-clicking on the VBA Project-> Click on Insert-> Click on Module.
Step 3: The Moule window will be inserted. Introduce the macro name followed by the declaration of the variable. Next, we will introduce a method named as Result().
Refer to the below given macro code:
Step 4: We will call the Split function, pass the string ‘Textstr’ in it and store the value in the result method.
Refer to the below given macro code:
Step 5: Using the UBound function to find the upper bound of the array. Since the output array base is 0 so we will add 1 to the value and display the returned word count using a MsgBox.
Output
Run the code by pressing the F5 key and fetching the output. As a result, it will return the message box displaying the word count as 7.
SPLIT Example 3 – Using a comma Delimiter to split the String
In the previous two examples, you will notice we have only used the Expression argument, and in the delimiter parameter, we have been using the default space character. Let’s see an example where we will be using another delimiter.
Below given are the steps to write a VBA macro to return an array based on a comma as the delimiter:
Step 1: Go to the VBA developer tab either by clicking the shortcut keywords Alt +F11 or click on developer window -> visual basic editor.
Step 2: The VB Editor will appear. The next step is to create a module. Right-clicking on the VBA Project-> Click on Insert-> Click on Module.
Step 3: The Moule window will be inserted. Introduce the macro name followed by the declaration of the variable. Next, we will introduce a method named as Result().
Refer to the below given macro code:
Step 4: We will call the Split Function, in the parameters we will pass the string and the delimiter (,). Next we will use the UBOUND and LBOUND function to find the upper and lower bound of the array. Later we will use the For Next loop to go through each substring of the ‘Result’ array.
Code:
Output
Run the code by pressing the F5 key and fetching the output. As a result, it will return the message box displaying all the substrings.
