How to use Split Function in Excel VBA Easily (4 Examples)

Welcome to Excel Avon

Split Function in Excel VBA?

DOWNLOAD USED EXCEL FILE FROM HERE>>

So, today’s post How to Use Split Functions in Excel VBA the Split function in VBA is a very useful string function. As the name Split suggests, Split is a function that is used to split strings into multiple substrings. can go. Of course, there are other string functions as well, which convert a string to a substring. But the split function can split a string into more than one substring. This is one of the best functions in VBA for performing a variety of operations on strings.

SPLIT is a built-in function in Excel VBA that can split a given sentence based on the delimiter. So, for example, if you have a pin code (500081,030001, 07071), the common element in the pin code is “,” in all pin code, so here “,” is a delimiter. becomes.

To understand for split Function in Excel VBA, we have to go to VBE Like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below.

On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.

Split Function Syntax

Like all other functions, the split function has a syntax.

Split (Expression as String, [Delimeter], [Limit as Long = 1], [Compare as vbComparemethod = vbCompareBinary]

Expression: Actual value we are trying to split.

Delimiter: This is an optional argument. It is the character that is used to break strings into parts.

[Limit]: How many substrings do you want from the value or expression you have supplied?

[Compare]: Since we don’t use the compare argument, skip this optional argument.

To understand Split Function in Excel VBA, we will write a subroutine for Split Function in Excel VBA after inserting the module in VBE.

Sub UseSplit()

End Sub

After writing the subroutine we will define the variable

Sub UseSplit()
Dim InpStr as String
Dim tArr as variant

End Sub

After we define the variable, For the InpStr we will store some text.

Sub UseSplit() 
Dim InpStr as String 
Dim tArr as variant

InpStr = "He is going to school"

End Sub

 We will use Split function to find the value.

Sub UseSplit() 
Dim InpStr as String 
Dim tArr as variant

InpStr = "He is going to school"
tArr = Split(inpStr)
End Sub

And now we will use debug.Print to print the value result in the Immediate window

Sub UseSplit() 
Dim InpStr as String 
Dim tArr as variant

InpStr = "He is going to school"
tArr = Split (InpStr)

debug.print tArr(0)
End Sub

And we will click run button

The SPLIT function returns an array of strings as output.

Now we will print the result using tAray(1)

Sub UseSPlit() 
Dim InpStr as String
Dim tArr as variant

InpStr = "He is going to school" 

tArr = Split (InpStr)
debug. Print tArr(1) 
End Sub

And we will click run button

Again, SPLIT function returns an array (1) of strings as output.

Now we will remove the text stored in InpStr and add some numbers along with the text and no. Let’s add a character to split

Sub UseSplit() 
Dim InpStr as String 
Dim tArr as variant 

InpStr = "Serial No-009-008-675"
End Sub

Now we will use split function along with delimiter criteria

Sub UseSplit() 
Dim InpStr as String 
Dim tArr as variant 

InpStr = "Serial No-009-008-675"

tArr = Split (InpStr, "-")
End Sub

By Using Delimiter Criteria, we can separate group wise in string.

Sub UseSplit() 
Dim InpStr as String 
Dim tArr as variant 

InpStr = "Serial No-009-008-675"

tArr = Split (InpStr, "-")
debug. Print tArr (1)
End Sub

Click Run Button for Print tArr(1) in Immediate window.

So, when we print tArr we will Get the value in Immediate Window

Now we have to use more than one character in Array to split.

Sub UseSplit() 
Dim InpStr as String 
Dim tArr as variant 

InpStr = "Serial No [-]009[-]008[-]675" 
tArr = Split (InpStr, "-")

debug. Print tArr (1)
End Sub

We will use more character for delimiter.

Sub UseSplit() 
Dim InpStr as String 
Dim tArr as variant 

InpStr = "Serial No [-]009[-]008[-]675"
tArr = Split (InpStr, "[-]")

debug. Print tArr (1)
End Sub

Click Run Button for Print tArr(1) in Immediate window.

So, when we print tArr(1) we will Get the value in Immediate Window.

‘Limit’ Criteria Uses

Now we will use ‘Limit’ Criteria with delimiter criteria.

Sub UseSplit() 
Dim InpStr as String 
Dim tArr as variant 

InpStr = "Serial No-009-008-675" 
tArr = Split (InpStr, "-",2) 

debug. Print tArr (1) 
End Sub

Click Run Button for Print tArr(1) in Immediate window.

we can print substring using text character with ‘Limit’ Character. its split the first one based on the hyphen delimiter. the rest becomes the second element of the array.

And then we will add some text in array. and remove limit. and add delimiter criteria.

Sub UseSplit() 
Dim InpStr as String 
Dim tArr as variant 

InpStr = "Serial No abc 009-008-675" 
tArr = Split (InpStr, "abc") 

debug. Print tArr (1) 
End Sub

After clicking the Run button, the array will be printed in the Immediate window.

Compare uses 

If we separate the text of the delimiter from the text given in the array, will this split result be printed or not it will not print but it will give an error message in the code so we will use vbTextCompare.

Sub UseSplit() 
Dim InpStr as String 
Dim tArr as variant 

InpStr = "Serial No abc 009-008-675" 
tArr = Split (InpStr, "ABC", , vbTextCompare) 

debug. Print tArr (1) 
End Sub

After clicking the Run button, the array will be printed in the Immediate window. using vbtextCompare if we write delimiter in any case then it will not give error.

The SPLIT function automatically thinks of the delimiter as space if one does not supply the delimiter.

So, I hope you have understood How to use Split Function in Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.

DOWNLOAD USED EXCEL FILE FROM HERE>>

LEARN MORE TOPIC IN VBA HERE

You can also see well-explained video here about Split Function in Excel VBA