VBA Split Function

As the name suggests Split is a function which splits strings into different parts. We have many such functions in excel worksheets such as left right and mid function to do so. But when we need any string to differentiate in parts we use a Split function in VBA. It is one of the best function in VBA to perform different types of operations on strings.

The split function is basically a substring function which takes a string as an input and gives another string as an output. The only difference between the other substring function like left right and mid and split function is that the LEFT, RIGHT & MID function just take one string as an input or argument and returns one string as an output while SPLIT function returns an array of strings as output.

Formula for Split Function in Excel VBA

VBA Split function has the following syntax:

Let me explain the arguments for the VBA split function first:

Expression As String: This is a mandatory argument in VBA Split function. Expression as string refers to the string we want to break into parts.
Delimiter: This is an optional argument. It is the character which is used to break strings into parts. But if we do not provide any delimiter VBA treats space “ “ as default delimiter.
Limit: This is also an optional argument. Limit means the maximum number of parts we want to do of a string. But again if we do not provide a limit to the function VBA treats it as default -1 which means the string will break apart each time there is a delimiter in the string.
Compare: This final argument is also an optional argument. Compare is a method which is described as one of the two below:

Either it is 0 which means Split will perform a binary comparison which means every character should match itself.
Or it can be 1 which means the Split function will do a textual comparison.

Everything will be clear in a few examples. But let me give a very basic example first what this function does. Suppose we have an input string as ANAND IS A GOOD BOY. The split string will break it in parts each word separately. We can also use the Split function to count a number of words in a string or we can use it to output only a certain amount of words in a given string.

How to Use Excel VBA Split Function?

We will learn how to use a VBA Split Excel function with few examples.

You can download this VBA Split Excel Template here – VBA Split Excel Template

VBA Split Function – Example #1

How about we use the above string ANAND IS A GOOD BOY with split function.

Note: In order to use a Split function in VBA make sure that developer option is turned on from File Tab from the options section.

Step 1: Go to the Developer tab click on Visual Basic.

Step 2: A project window appears to click on Sheet 1 to open the code window.

Step 3: When the code window appears, declare a sub-function to start writing the code.

Code:

Sub Sample()

End Sub

Step 4: Declare two variables arrays and one as strings A & B.

Code:

Sub Sample()

Dim A As String
Dim B() As String

End Sub

Step 5: Store the value of the string in A.

Code:

Sub Sample()

Dim A As String
Dim B() As String
A = "ANAND IS A GOOD BOY"

End Sub

Step 6: In B array store the value of A using the split function as shown below.

Code:

Sub Sample()

Dim A As String
Dim B() As String
A = "ANAND IS A GOOD BOY"
B = Split(A)

End Sub

Step 7: Use For Loop to break every string.

Code:

Sub Sample()

Dim A As String
Dim B() As String
A = "ANAND IS A GOOD BOY"
B = Split(A)
For i = LBound(B) To UBound(B)
  strg = strg & vbNewLine & "String Number " & i & " - " & B(i)
Next i

End Sub

Step 8: Display it using the Msgbox function.

Code:

Sub Sample()

Dim A As String
Dim B() As String
A = "ANAND IS A GOOD BOY"
B = Split(A)
For i = LBound(B) To UBound(B)
  strg = strg & vbNewLine & "String Number " & i & " - " & B(i)
Next i
MsgBox strg

End Sub

Step 9: Run the code from the run button provided below.

We get this as output once we run the above code.

VBA Split Function – Example #2

We will now try to take input from a user and split the string into parts.

Step 1: Go to the developer’s tab and click on Visual Basic to open the VB Editor.

Step 2: Click on Sheet 2 from the properties window to open the code window.

Step 3: In the code window declare a sub-function to start writing the code.

Code:

Sub Sample1()

End Sub

Step 4: Declare two variables one as String and one as an Array String.

Code:

Sub Sample1()

Dim A As String
Dim B() As String

End Sub

Step 5: Take the value from the user and store it in the A using Inputbox function.

Code:

Sub Sample1()

Dim A As String
Dim B() As String
A = InputBox("Enter a String", "Should Have Spaces")

End Sub

Step 6: Store the value of A in Array B using the Split Function.

Code:

Sub Sample1()

Dim A As String
Dim B() As String
A = InputBox("Enter a String", "Should Have Spaces")
B = Split(A)

End Sub

Step 7: Use For Loop to break every string.

Code:

Sub Sample1()

Dim A As String
Dim B() As String
A = InputBox("Enter a String", "Should Have Spaces")
B = Split(A)
For i = LBound(B) To UBound(B)
  strg = strg & vbNewLine & "String Number " & i & " - " & B(i)
Next i

End Sub

Step 8: Display it using the Msgbox function.

Code:

Sub Sample1()

Dim A As String
Dim B() As String
A = InputBox("Enter a String", "Should Have Spaces")
B = Split(A)
For i = LBound(B) To UBound(B)
  strg = strg & vbNewLine & "String Number " & i & " - " & B(i)
Next i
MsgBox strg

End Sub

Step 9: Run the code from the run button. Once we run the code we get an input message to write a string. Write “I AM A GOOD BOY” as input in the input box and press ok to see the result.

VBA Split Function – Example #3

We can also use the VBA Split Function to count the number of words in the string. Let us take input from the user and count the number of words in it.

Step 1: Go to the developer’s tab and click on Visual Basic to open VB Editor.

Step 2: Click on Sheet 3 in the project window to open the code window.

Step 3: Once the code window is open declare a sub-function to start writing the code.

Code:

Sub Sample2()

End Sub

Step 4: Declare two variables one as a string and one as an array string.

Code:

Sub Sample2()

Dim A As String
Dim B() As String

End Sub

Step 5: Take input from the user and store in A using the input box function.

Code:

Sub Sample2()

Dim A As String
Dim B() As String
A = InputBox("Enter a String", "Should Have Spaces")

End Sub

Step 6: Use the Split function and store it in B.

Code:

Sub Sample2()

Dim A As String
Dim B() As String
A = InputBox("Enter a String", "Should Have Spaces")
B = Split(A)

End Sub

Step 7: Use a Msgbox function to display the total number of words.

Code:

Sub Sample2()

Dim A As String
Dim B() As String
A = InputBox("Enter a String", "Should Have Spaces")
B = Split(A)
MsgBox ("Total Words You have entered is : " & UBound(B()) + 1)

End Sub

Step 8: Run the code from the run button provided. Once we have run the code it asks for an input for the string. Write “INDIA IS MY COUNTRY” in the box and press ok to see the result.

Explanation of Excel VBA Split Function

Now we know that split function in VBA is a substring function which is used to split strings into different parts. The input we take is as a string while the output displayed is an array.

It is very similar to the other worksheet function but it is superior as it can break multiple words and return them as an array.

Things to Remember

There are a few things we need to remember about VBA split function:

The VBA split function is a substring function.
It returns output as a string.
Only the expression is the mandatory argument while the rest of the arguments are optional.