VBA Split Function

What is VBA Split Function in Excel?

The VBA Split function is built-in in Excel’s Visual Basic for Applications (VBA) programming language. It allows you to split a string into multiple substrings based on a specified delimiter. This function is beneficial when you have a string containing multiple values or elements separated by a specific character or sequence of characters. You need to extract and work with individual components.

VBA Split String Function

The VBA Split function syntax is straightforward:

Split(Expression, [Delimiter], [Limit], [Compare])

• Expression: The string that you want to split into substrings
• Delimiter: The character or sequence of characters determining where the split should occur.
• Limit (optional): An integer value specifying the maximum number of substrings to return. If omitted or set to -1, all possible substrings will be returned.
• Compare (optional): An optional value specifying the string comparison method. It can be set to vbBinaryCompare (binary comparison) or vbTextCompare (text comparison). If omitted, text comparison is used by default.

Example

Let’s look at an example where we utilize the VBA Split function to split a data string (“Apple, Orange, Mango, Banana”) into individual values using a comma as the delimiter. Then, using the UBound function, we can determine the count of the values obtained.

Finally, we will display the number of values in a message box using the provided VBA code.

Once you run the above code, it displays the number of values in a message box.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples of VBA Split String Function

Let’s explore some examples to understand the practical usage of the VBA Split function.

Example #1 – Split the Sentence

In this example, we will use the VBA Split function to split a sentence into individual words. The resulting words will be written to the first column of Sheet 1 in an Excel workbook, starting from cell A1. Refer to Module 1 in the “VBA Split function” workbook.

• Step 1: Open the VBA Editor by pressing Alt + F11 in Excel.

• Step 2: Insert a new module by clicking “Insert” from the top menu and selecting “Module.”

• Step 3: Create a Subroutine named “SplitTheSentence” using the Sub keyword. And declare three variables: sentence as a string variable to hold the sentence to be split, words() as a dynamic array of strings to store the resulting words, and “I” as an integer variable to serve as a counter for the loop.

• Step 4: Now, we will assign the sentence we want to split to the sentence variable. Modify the sentence accordingly to fit your desired input. In the example, we will use “Hello, how are you today?”

• Step 5: Let’s use the Split function to split the sentence into words. The Split function takes two parameters: the first parameter is the string to be split (sentence), and the second parameter is the delimiter (” “) which specifies where the split should occur. In this case, we use a space as the delimiter. The resulting words are stored in the words() array.

• Step 6: Now, let’s use a VBA loop to iterate through each word in the words() array. The loop starts from 0 to the upper bound of the words() array, obtained using the UBound function.

We access each word in the loop using words(i) and write it to the first column (Column A) of Sheet1. The Sheet1.Cells(i + 1, 1) represent the cell at the ith row and 1st column of Sheet1. The word is assigned to the .Value property of the cell.

This loop repeats for each word in the words() array, writing them to successive rows in the first column of Sheet1.

• Step 7: Now save the module and exit the VBA editor. Now Press Alt + F8 to open the Macro box and select “SplitTheSentence,” and click on Run.

• Step 8: After executing the macro in excel, you will observe that the words from the sentence “Hello, How are you today?” are split and populated into cells A1 to A5 in the Excel worksheet.

Here is the full code:

Sub SplitTheSentence()
Dim sentence As String
Dim words() As String
Dim i As Integer
‘ Assign the sentence to be split
sentence = “Hello, how are you today?”
‘ Split the sentence into words using space as the delimiter
words = Split(sentence, ” “)
‘ Write the words to the first column of Sheet1 starting from cell A1
For i = 0 To UBound(words)
Sheet1.Cells(i + 1, 1).Value = words(i)
Next i
End Sub

Example #2 – VBA SPLIT String with UBOUND Function

In this example, we will use the VBA Split function to split a sentence into individual words. Also, we use the “UBound(words)” function, which is used to determine the upper bound of the resulting array of words.

The words will be displayed in the Immediate window, allowing you to easily view the result without needing an Excel worksheet.

• Step 1: In a new module, assign a subroutine “Sub SplitSentence()” and declare the necessary variables to hold the sentence, resulting “substrings” and “loop” counter.

In this step, we declare three variables. The “sentence” variable is used to hold the sentence that we want to split, the “words()” array is used to store the resulting substrings, and the “i” variable is used as a counter in the loop.

• Step 2: In this step, we assign the sentence you want to split to the sentence variable. You can replace the text within the double quotes with any sentence you want to work with.

• Step 3: Use the Split function to split the sentence by space and store the resulting substrings in the words() array.

The Split function splits the sentence variable into multiple substrings based on the space character (” “). The resulting substrings are then stored in the words() array.

• Step 4: In this step, we will use a loop to iterate through each element of the words() array. The “UBound(words)” function returns the upper bound of the array (the index of the last element). The loop starts from 0 and continues until the last index of the array. Finally, we use the “Debug.Print” statement within the loop to print each word on a separate line in the Immediate window.

• Step 5: Now save the macro and click on the “Run” button, as shown below in the image.

• Step 6: After you run the code, you will notice that the words are split and appear in the “Immediate” window.

Note: To display the Immediate window in VBA, go to the “View” menu in the VBA Editor and select “Immediate Window” or press Ctrl + G.

The Immediate window in VBA is a debugging tool that allows you to view and interact with the output of your code, display variable values, and execute Immediate statements.

Here is the full code:

Sub SplitSentence()
Dim sentence As String
Dim words() As String
Dim i As Integer
‘ Assign the sentence string
sentence = “The quick brown fox jumps over the lazy dog.”
‘ Split the sentence into words using space as the delimiter
words = Split(sentence, ” “)
‘ Display the words in the Immediate window
For i = 0 To UBound(words)
Debug.Print words(i)
Next i
End Sub

Important Things to Note

• The Split function extracts specific portions of a string. It allows you to break down a string into an array of substrings, which can be accessed individually or processed further.
• By default, if the input string contains consecutive delimiters or begins or ends with a delimiter, the Split function will create empty substrings in the resulting array. Consider how these empty substrings might impact your code and handle them appropriately.
• Providing the correct syntax while using the VBA Split function is crucial. Incorrect syntax can lead to unexpected results or errors, hampering the desired outcome.
• The Split function returns an array of substrings. Assigning the result to an appropriate VBA data type, such as a variant or string array, and ensuring compatibility with other parts of your code that may interact with the split data is essential.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Split Function In Excel, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Split Function. Here we learn how to split strings in excel VBA using Split Function, with examples & downloadable excel template. You can learn more from the following articles –

• VBA Concatenate
• VBA Columns
• VBA String Functions