VBA Split Function

The Split function in VBA is a very useful string function that one can use to split strings into multiple substrings based on a delimiter provided to the function and a comparison method. Of course, there are other string functions, too, which convert a string into a substring. But, the VBA Split function can split a string into more than one substring.

In a normal worksheet, the LEFT function, MID functions, and RIGHT in excel are used as text functions to extract the portion of the sentence. For example, extraction of first, middle, and last names is the common scenario we have seen. 

But in VBA, we have the more versatile SPLIT function, which will do a similar job and much more. SPLIT is a built-in function in Excel VBA that can split the supplied sentence based on the delimiter supplied. For example, if you want to split an email address into different parts, the common element in the email address is "@" in all the email IDs, so "@" becomes a delimiter here.

Like all other functions, split has its syntax. For example, below are the Excel VBA Split string function parameters.

Arguments:

• Expression (mandatory) - The string you want to split. (Required)
• Delimiter (Optional) -The character that separates each substring. Default is space. 
• Limit (Optional) - Maximum number of substrings to return.
• Compare (Optional) - Type of comparison: binary (vbBinaryCompare) or text (vbTextCompare). 

In the next sections of this article, we will see how to use the SPLIT function in Excel VBA practically.

Below are the practical examples of the Split function in Excel VBA.

Example #1 - Split the Sentence

The Split function returns the result in the array, which will start from 0. All the arrays start from 0, not from 1.

Assume you have the word "My Name is Excel VBA" in cell A1.

Now, you want to split this sentence into pieces like "My," "Name," "is," "Excel," and "VBA." Then, we can return this result using the Excel VBA SPLIT String function.

Step 1: Start a macro with the name shown below.

Code:

Sub Split_Example1()

End Sub

Step 2: Declare three variables.

Code:

Sub Split_Example1()  

Dim MyText As String  

Dim i As Integer

Dim MyResult() As String

End Sub

Step 3: Now, for the defined variable, My Text assigns the word "My Name is Excel VBA."

Code:

Sub Split_Example1()

Dim MyText As String

Dim i As Integer

Dim MyResult() As String  

MyText = "My Name is Excel VBA"

End Sub

Step 4: Now, apply the VBA Split function for the "My Result" variable.

Code:

Sub Split_Example1()

Dim MyText As String

Dim i As Integer

Dim MyResult() As String  

MyText = "My Name is Excel VBA"

MyResult = Split(

End Sub

Step 5: Expression is our text value. Since we have already assigned our text value to the variable “MyText,” enter this argument here.

Code:

Sub Split_Example1()

Dim MyText As String

Dim i As Integer

Dim MyResult() As String  

MyText = "My Name is Excel VBA"  

MyResult = Split(MyText)

End Sub

Note: As of now, ignore all the other parameters.

Step 6: Now, "My Result" holds this split result. As we told earlier in the post, the Split function stores the result as an array, so here:

• My Result (0) = “My”
• My Result (1) = “Name”
• My Result (2) = “is”
• My Result (3) = “Excel”
• My Result (4) = “VBA”

Even though this code does not impact starting the SPLIT function, we can use this code.

Example #2 - VBA SPLIT String with UBOUND Function

To store the result of the SPLIT function, we can use the VBA UBOUND function along with the SPLIT function.

The UBOUND function will return the maximum length of the array. In the above example, the maximum length of the array was 5.

Take the same word "My Name is Excel VBA." Let us split this word and store it from cell A1 onwards.

Step 1: Let us continue from where we left off in the previous example.

Step 2: Now, apply FOR NEXT LOOP in VBA from 0 to the maximum length of the array, UBOUND.

We started from zero because SPLIT will store the result from zero, not from 1.

Step 3: Now, apply the VBA CELLS property and store the result.

Code:

Cells(i + 1, 1).Value = MyResult(i)

Step 4: Run this code. We would have split values.

Complete Code:

Sub Split_Example1()  

Dim MyText As String  

Dim i As Integer

Dim MyResult() As String  

MyText = "My Name is Excel VBA"

MyResult = Split(MyText)

For i = 0 To UBound(MyResult)      

Cells(i + 1, 1).Value = MyResult(i)  

Next i

End Sub

Return Word Count

We can also show the total number of words in the supplied value. Use the below code to show the total number of word counts.

Sub Split_Example2()

Dim MyText As String

Dim i As Integer

Dim MyResult() As String  

MyText = "My Name is Excel VBA"  

MyResult = Split(MyText)  

i = UBound(MyResult()) + 1  

MsgBox "Total Words Count is " & i

End Sub

Please copy and paste the above VBA code and run it. The message box will return the result.

Using a delimiter other than space

Besides the above important examples, you can also use delimiters other than space to split text. 

Sub Split_Example3()

    Dim fruits As String0

    Dim fArray() As String

    Dim i As Integer

    ' Text with commas separating items

   fruits = "Apple,Pineapple,Orange,Grapes,Mango"

    ' Split the text into an array based on the comma delimiter

   fArray = Split(fruits, ",")

    ' Loop through the array and print each fruit

    For i = LBound(fArray) To UBound(fArray)

       Debug.Print fArray(i)

    Next i

End Sub

Here, we assign a text string with fruits separated by commas.

Using the SPLIT function, we split the fruits string into an array wherever it finds a comma.

Then, we loop from the first element to the last of the array and print each fruit separately.

• The SPLIT function automatically thinks of the delimiter as space if one does not supply the delimiter.
• To split except space, you must specify the delimiter in double quotes.
• The SPLIT function stores the result as array results.
• The UBOUND function returns the maximum length of the array.