EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA String and Text Functions VBA Split
 

VBA Split

Madhuri Thakur
Article byMadhuri Thakur

VBA Split Function

VBA Split Function

As the name suggests, a Split is a function that splits strings into different parts. We have many such functions in excel worksheets, such as a 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 functions in VBA to perform different types of operations on strings.

 

 

The split function is basically a substring function that 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 the SPLIT function returns an array of strings as output.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Formula for Split Function in Excel VBA

VBA Split function has the following syntax:

Syntax of Split

Given below are 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 that 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 that is described as one of the two below:
  1. Either it is 0, which means Split will perform a binary comparison which means every character should match itself.
  2. 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 of what this function does. Suppose we have an input string as ANAND IS A GOOD BOY. The split string will break it into 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 see 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 the developer option is turned on from File Tab from the options section.

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

Example 1-1

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

VBA Split Example 1-2

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

Code:

Sub Sample()

End Sub

VBA Split Example 1-3

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

VBA Split Example 1-4

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

VBA Split Example 1-5

Step 6: In the 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

VBA Split Example 1-6

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

VBA Split Example 1-7

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

VBA Split Example 1-8

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

Result of Example 1-9

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.

Example 2-1

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

Code:

Sub Sample1()

End Sub

VBA Split Example 2-2

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

VBA Split Example 2-3

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

Code:

Sub Sample1()

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

End Sub

VBA Split Example 2-4

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

VBA Split Example 2-5

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

VBA Split Example 2-6

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

VBA Split Example 2-7

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.

Result of Example 2-8

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.

Example 3-1

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

Code:

Sub Sample2()

End Sub

VBA Split Example 3-2

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

VBA Split Example 3-3

Step 5: Take input from the user and store it 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

VBA Split Example 3-4

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

VBA Split Example 3-5

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

Example 3-6

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.

Result of Example 3-7

Explanation of Excel VBA Split Function

Now we know that the split function in VBA is a substring function that 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 the output as a string.
  • Only the expression is the mandatory argument, while the rest of the arguments are optional.

Recommended Articles

This has been a guide to VBA Split Function. Here we discussed how to use Excel VBA Split Function along with practical examples and a downloadable excel template. You can also go through our other suggested articles to learn more –

  1. VBA While Loop
  2. VBA CDEC
  3. VBA Do While Loop
  4. VBA LBound

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download VBA Split Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW