When dealing with VBA Excel, the user may need to split a string into substrings according to a given delimiter. For instance, if given a location, you can use the VBA Split function to separate different parts of the place with a space (in this case, the space is the delimiter).
The SPLIT String function divides a string into substrings based on the delimiter that is given. It gives back a one-dimensional array that starts at 0 and holds the parts of substrings. The VBA Split function is one of the String/Text Functions that come with the programme. In the Microsoft Visual Basic Editor, the code for this function is written in VBA macro. But you can also use Split in Excel spreadsheets as a function.
Syntax
Split (Expression, [Delimiter], [Limit], [Compare])
Parameter
1. Expression (Required)
The parameter is the input string that you will split based on the delimiter.
2. Delimiter (Optional)
This parameter shows the delimiter used to split the String. It usually includes the “Expression” argument. For example, suppose you have a string “Hello, World! “. In this case, a comma is a delimiter with the Split function to different substrings. If you don’t give a value for this argument, the default is the space character.
3. Limit (Optional)
An optional parameter lets the user say how many substrings they want to return in total. For example, if you only want to get the first three substrings from the String, you would put 3 in this argument. If you don’t give a value for this argument, the default is -1, which provides you with all the substrings.
4. Compare (Optional)
It returns the type of comparison the user wants to use in the SPLIT function when evaluating the substrings.
- When Compare is 0, it means that the comparison is binary. It’s useful when your delimiter is a text string, like “XYZ,” since this is case-sensitive. The letters ‘XYZ’ and ‘xyx’ will look different.
- When Compare is 1, 1 shows the Text comparison. It helps if your delimiter is a text string, like XYZ. Though you have “xyz” in the “Expression” String, it will be seen as a delimiter.
See More: VBA MID Function
Examples
Split the String by the Default Space Delimiter
VBA Split is a built-in function that helps quickly divide a string into substrings based on the delimiter that is given. The following are the steps to creating a VBA macro to use the Split function to divide your String:
Step 1: Navigate to the VBA developer tab or press Alt + F11, which is a keyboard shortcut, or by going to developer window > visual basic editor.
Step 2: You’ll see the VB Editor. Next, you need to do a module. Right-click on the VBA Project, then click Insert, then click Module.
Step 3: Insert the Moule window. Create the macro and then declare the variable. Following that, present a method known as Result ().
Check out the macro code below:
Sub VBA_Split_Function() 'Declaring a variable Dim StrText As String Dim StrResult() As String StrText = "Hello, Welcome to the XL Automation of VBA programming Tutorial." End Sub
Step 4: Call the Split function, give it the string “Textstr,” and store the value in the result method.
Check out the macro code below:
Sub VBA_Split_Function() 'Declaring a variable Dim StrText As String Dim StrResult() As String StrText = "Hello, Welcome to the XL Automation of VBA programming Tutorial." 'Splitting the String using the split function using the default space delimiter StrResult() = Split (StrText) End Sub
Output
Tap the F5 key to execute the code and get the results. As a result, the Split function breaks up the string ‘StrText’ into smaller strings and stores each in the StrResult array.
The following substrings will be available in the backend:
