Trending October 2023 # How To Use Excel Vba Split Function? # Suggested November 2023 # Top 14 Popular

You are reading the article How To Use Excel Vba Split Function? updated in October 2023 on the website Khongconthamnam.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 How To Use Excel 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:

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:

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 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 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

As String

Dim

B()

As String

End Sub

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

Code:

Sub

Sample()

Dim

As String

Dim

B()

As String

A = "ANAND IS A GOOD BOY"

End Sub

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

Code:

Sub

Sample()

Dim

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

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)

End Sub

Step 8: Display it using the Msgbox function.

Code:

Sub

Sample()

Dim

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)

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 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

As String

Dim

B()

As String

End Sub

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

Code:

Sub

Sample1()

Dim

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

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

As String

Dim

B()

As String

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

For

i =

LBound

(B)

To UBound

End Sub

Step 8: Display it using the Msgbox function.

Code:

Sub

Sample1()

Dim

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)

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 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

As String

Dim

B()

As String

End Sub

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

Code:

Sub

Sample2()

Dim

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

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

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 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.