Excel provides several VBA functions that can be used while writing a macro or to define your own function to perform your desired tasks. In this article, we are going to show you a VBA function called SPLIT.
Download Practice Workbook
You are welcome to download the practice workbook from the link below.
Introduction to VBA SPLIT Function
The VBA SPLIT function in Excel splits the string into several substrings and returns a one-dimensional array of substrings.
SPLIT( expression, [delimiter], [limit], [compare] )
|expression||Required||The string to split based on the delimiter|
|delimiter||Optional||The character that divides the string into parts|
|limit||Optional||Number of substrings to be returned|
|compare||Optional||Numeric value to indicate what type of comparison to use when evaluating substrings
0 – Binary Comparison
1– Textual Comparison
5 Suitable Examples to Use VBA SPLIT Function
In the following sections, we will demonstrate five examples for describing the VBA SPLIT function. In these examples, we will apply the VBA SPLIT function for splitting words from a string, splitting words separated with multiple delimiters, dividing a string into multiple parts, counting words from a string, and finding specific words from a string into an array.
1. Split Words from String by Applying VBA SPLIT Function
As per the description of the function, you have understood that the SPLIT function will split the string into substrings. Let’s follow the instructions below to learn!
- First of all, open a Module, to do that, firstly, from your Developer tab, go to,
Developer → Visual Basic
- After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – VBA SPLIT Function will instantly appear in front of you. Further, from that window, we will insert a module for applying our VBA code. To do that, go to,
Insert → Module
- Hence, the VBA SPLIT Function module will appear in front of you. In the VBA SPLIT Function module, write down the below VBA code.
Sub Split_Word() Dim Text As String Dim Result() As String Dim VisualText As String Text = "We are learning VBA SPLIT Function." Result = Split(Text) For i = LBound(Result()) To UBound(Result()) VisualText = VisualText & Result(i) & vbNewLine Next i MsgBox VisualText End Sub
Here we set the name of the macro as Split_Word. Then, we declared three variables using the Dim keyword. All the variables are string types.
We have set the string We are learning VBA SPLIT Function in one of the variables (Text). Then applied the SPLIT function to the string using that variable and stored it in a variable named Result.
Here we have used a For loop where LBound and UBound set the range. LBound is the least length a string can have (0) and UBound returns the length of the string.
This loop helps us to store the substring in the VisualText variable. Then, we added a MsgBox to return the output in a message box format.
- After that, run the VBA code. To do that, go to,
Run → Run Sub/UserForm
- After running the VBA Code, you will see the output in a message box that has been given in the below screenshot.
- Since we didn’t mention any delimiter, it assumed each substring was up to a space. If you want the output in cells rather than a message box then use the cell number instead of MsgBox. Now, paste the below VBA code into that module.
Sub Split_Word_from_Text() Dim Text As String Dim Result() As String Dim VisualText As String Text = "We are learning VBA SPLIT Function." Result = Split(Text) For i = LBound(Result()) To UBound(Result()) Cells(i + 4, 2).Value = Result(i) Next i End Sub
- Here we have added the value into the Cells, where i+3 and 2 are the row and column numbers respectively. After that, Run the code.
- As a result, the output will be on the Excel cells which have been given in the below screenshot.
2. Apply VBA SPLIT Function For Splitting Words Separated with Multiple Delimiters
If we don’t use any specific delimiter, then the SPLIT function will split assuming space as the delimiter. A delimiter can be a comma, a semicolon, or any separating character. Here we are using a comma as the delimiter.
Now we will split words that are separated with multiple delimiters. Let’s follow the instructions below to learn!
- According to method 1, insert a new module and type the below VBA code to add an Excel sheet with the name. The VBA code is,
Sub Comma_Separator() Dim Text As String Dim Result() As String Dim VisualText As String Text = "Apples, Bananas, Oranges, Mangoes" Result = Split(Text) For i = LBound(Result()) To UBound(Result()) Cells(i + 4, 2).Value = Result(i) Next i End Sub
- You can see the only change in our code is we have used the delimiter field in the SPLIT function and inserted a comma (,) there. This denotes that the string will be split into the comma.
- After that, press the F5 key to Run the code and you will see the output.
- How to Call a Sub in VBA in Excel (4 Examples)
- Return a Value in VBA Function (Both Array and Non-Array Values)
- How to Use VBA DIR Function in Excel (7 Examples)
- Use the Left Function in VBA in Excel (2 Examples)
- How to Use VBA StrConv Function (5 Examples)
3. Divide into Multiple Parts by Using SPLIT Function in Excel VBA
We can divide the strings into multiple parts, it seems a bit similar to the split string into substrings. This time we are going to use the SPLIT in our own defined function.
Here is an example of a dataset consisting of a few addresses. Each address contains three parts.
We are set to divide these three parts into one each. Let’s follow the instructions below to learn!
- Our code will be,
Function ThreePartAddress(cellRef As Range) Dim TStrng As String Dim Result() As String Dim VisualText As String Result = Split(cellRef, ",", 3) For i = LBound(Result()) To UBound(Result()) VisualText = VisualText & Trim(Result(i)) & vbNewLine Next i ThreePartAddress = Mid(VisualText, 1, Len(VisualText) - 1) End Function
- Within our code, we have inserted the limit field in the SPLIT Since we want to divide it into three parts, we have used 3 as the limit parameter.
- We have used VBA LEN and MID functions that return the length of the string and extract the middle value from the text.
- Hence, save the code and use the function.
- Use the function for any of the rows. Let’s for the first row of our dataset.
- First of all, select cell C5, and Enter the below user-defined function. The function is,
- After that, we will find the address separated into three parts.
- Further, AutoFill the function to the rest of the cells in column C.
4. Count Words from String Using VBA SPLIT Function
The SPLIT function will be helpful to count the words in a string. To show you examples, we have brought a dataset of a few random sentences.
We will find the total word of these sentences. We are going to create our own function using SPLIT.
- The code will be
Function WordCount(CRef As Range) Dim TextStrng As String Dim Result() As String Result = Split(WorksheetFunction.Trim(CRef.Text), " ") WordCount = UBound(Result()) + 1 End Function
- We have trimmed the text using the VBA TRIM function and split it by space (though we can skip this as by default split by space).
- In the WordCount variable, we have added the UBound of the string with 1.
- Hence, save the code and write the function for the sentence.
- After that, select cell C5, and Enter the below user-defined function. The function is,
- Hence, we have found the total number of words present in the sentence.
- Further, AutoFill the function to the rest of the cells in column C.
5. Find Specific Word from a String into Array with Excel VBA
Last but not least, we can find and retrieve any specific word from a string. Here, we are using the address dataset for this example.
Now, we are going to fetch the name of the city from the address. We can find any nth number of values from the string, for that we need to set the number of that particular element from the string. Let’s follow the instructions below to learn!
- And our code will be
Function FindNthValue(CRef As Range, ValueNum As Integer) Dim Result() As String Result = Split(CRef, ",") FindNthValue = Result(ValueNum - 1) End Function
- Here our function takes two parameters; the string and the value number. The SPLIT function splits the string by a delimiter comma. It returns the substrings in an array.
- The array index is one less than the total element. You can see our stored addresses have 4 parts separated by a comma. But the array index number will be one less than that (3). That’s why we have subtracted 1 from the provided value number.
- After that, Save the code and use the function.
- Hence, select cell C5, and Enter the below user-defined function. The function is,
- As a result, we provided the string and 3 (city at the 3rd position) and found the city name in return.
- After that, AutoFill the function to the rest of the cells in column C.
- You can pop up the Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously. You can open the Microsoft Visual Basic Applications window by using the ALT + L + V shortcut as well.
- If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,
File → Option → Customize Ribbon
That’s all for today. We have tried showing you how you can use the VBA SPLIT function. You will use the function to split the string into substrings as well as count the number of words from the string. Hope you will find this helpful.
Feel free to comment if anything seems difficult to understand. Let us know any of your SPLIT function-related scenarios where you have stuck, we are ready to help.