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.
You are welcome to download the practice workbook from the link below.
VBA SPLIT Function
1. Basics of VBA SPLIT
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
2. Uses of VBA SPLIT
I. Split Words from String
As per the description of the function, you have understood that the SPLIT function will split the string into substrings.
To split the string we will use the code written below.
Sub WordSeparator() Dim TextStrng As String Dim Result() As String Dim DisplayText As String TextStrng = "We are learning VBA SPLIT" Result = Split(TextStrng) For i = LBound(Result()) To UBound(Result()) DisplayText = DisplayText & Result(i) & vbNewLine Next i MsgBox DisplayText End Sub
Write the code at the Microsoft Visual Basic for Applications (You will find it under the Developer tab or can use the ALT + L + V shortcut to open the window)
Here we set the name of the macro as WordSeperator. Then declared three variables using the Dim keyword. All the variables are string types.
We have set the string We are learning VBA SPLIT in one of the variables (TextString). Then applied the SPLIT function to the string using that variable and stored it in variable 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 the UBound returns the length of the string.
This loop helps us to store the substring into the DisplayText variable. Then added a MsgBox to return the output in a message box format.
Run the code. You will see the output in a message box.
Since we didn’t mention any delimiter, it assumed each substring up to a space.
If you want the output in cells rather than a message box then use the cell number instead of MsgBox.
Here we have added the value into the Cells, where i+3 and 2 are the row and column numbers respectively. Now Run the code
The output will be on the Excel cells.
II. Split Words Separated with 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, semicolon, or any separating character.
Here we are using a comma as the delimiter.
Our code will be
Sub CommaSeparator() Dim TextStrng As String Dim Result() As String Dim DisplayText As String TextStrng = "Apples, Bananas,Oranges,Mangoes" Result = Split(TextStrng, ",") For i = LBound(Result()) To UBound(Result()) Cells(i+3, 2).Value = Result(i) Next i MsgBox DisplayText End Sub
You can see the only change of our code is we have used the delimiter field in the SPLIT function and inserted comma (,) there. This denotes that the string will be split into the comma.
Run the code and you will see the output.
III. Divide into Multiple Parts
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 our example dataset consists of few addresses. Each address contains three parts.
We are set to divide these three parts into one each. Our code will be,
Function ThreePartAddress(cellRef As Range) Dim TextStrng As String Dim Result() As String Dim DisplayText As String Result = Split(cellRef, ",", 3) For i = LBound(Result()) To UBound(Result()) DisplayText = DisplayText & Trim(Result(i)) & vbNewLine Next i ThreePartAddress = Mid(DisplayText, 1, Len(DisplayText) - 1) End Function
Within our code, we have inserted the limit field in the SPLIT function. Since we want to divide into three parts so we have used 3 at 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.
Now save the code and use the function.
Use the function for any of the rows. Let’s for the first row
We will find the address separated into three parts (see the image below).
You can use the function as many times as you want.
IV. Count Words using SPLIT
The SPLIT function will be helpful to count the words from 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(CellRef As Range) Dim TextStrng As String Dim Result() As String Result = Split(WorksheetFunction.Trim(CellRef.Text), " ") WordCount = UBound(Result()) + 1 End Function
We have trimmed the text using 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.
Save the code and write the function for the sentence.
We have found the total number of words present in the sentence.
You can write the function for the other sentences as well.
V. Find any Specific Word from a String
We can find and retrieve any specific word from a string.
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.
And our code will be
Function ReturnNthValue(CellRef As Range, ValueNumber As Integer) Dim Result() As String Result = Split(CellRef, ",") ReturnNthValue = Result(ValueNumber - 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.
Save the code and use the function.
We have provided the string and 3 (city at the 3rd position) and found the city name in return. A similar result will be for the rest of the values.
- Use the Left Function in VBA in Excel (2 Examples)
- 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)
- How to Use VBA StrConv Function (5 Examples)
3. Things to Remember
SPLIT stores the result as an array. From our first example, we have not set any output methods and so just write the code like this:
Sub WordSeparator() Dim TextStrng As String Dim Result() As String Dim DisplayText As String TextStrng = "We are learning VBA SPLIT" Result = Split(TextStrng) End Sub
Running this code you may not see any output but the string would be divided into substrings and be stored within the array.
That’s all for today. We have tried showing how you can use the VBA SPLIT function. You can 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.