What is VBA Split Function in Excel?
In normal worksheet functions LEFT, RIGHT, and MID functions are used as text functions to extract the portion of the sentence. For example extraction of first name, middle name, and the last name is the common scenarios we have seen. But in VBA we have the more versatile function called SPLIT which will do a similar job for you. SPLIT is a built-in function in Excel VBA which can split the supplied sentence based on the delimiter. For example, if you want to split the email address to different parts the common element in the email address is “@” in all the email id’s, so “@” becomes delimiter here.
VBA Split String Function
Like all the other functions split too has its own syntax. Below are the parameters of the excel VBA Split string function.
- Value or Expression: This nothing but the actual value we trying to split. For example, if you want to split first name and last name, the full name is the value here.
- [Delimiter]: What is the common element to split the Value or Expression? In email Id’s “@” is the common element, in address comma (,) is the common element. If you ignore this it considers the space character as the default value.
- [Limit]: How many substrings you want from the Value or Expression you have supplied. For example, if the value is “My name is Excel”, if you supply 3 as the limit it will show the result in three lines like “My”, “name”, “is Excel”.
- [Compare]: Since we don’t use compare argument skip this optional argument.
In the next sections of the article, we will see how to use the SPLIT function in excel VBA practically.
Examples of VBA Split String Function
Below are the practical examples of the Split Function in Excel VBA.
Example #1 – Split the Sentence
Split returns the result in the array which will start from 0. All the arrays are starts 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”, “VBA”. Using Excel VBA SPLIT String function we can return this result.
Step 1: Start the macro with the name.
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 for My Result variable apply VBA split string function.
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 variable My Text 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 on now ignore all the other parameters.
Step 6: So now My Result holds this split result. As I told earlier in the post, 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 make any impact to start off the SPLIT function we can use this code.
Example #2 – VBA SPLIT String with UBOUND Function
In order to store the result of the SPLIT function, we can use UBOUND function along with SPLIT function.
UBOUND will returns 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’s split this word and store from cell A1 onwards.
Step 1: Let’s continue from where we left off in the previous example.
Step 2: Now apply FOR NEXT LOOP in VBA from 0 to maximum length of the array i.e. UBOUND.
The reason we started from zero because SPLIT will store the result from zero, not from 1.
Step 3: Now apply 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. Code:
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
Copy and paste the above VBA code and run it, the message box will return the result.
Things to Remember
- If the delimiter is not supplied SPLIT automatically thinks the delimiter as space.
- If you want to split except space then you need to specify the delimiter in double quotes.
- SPLIT stores the result as array results.
- UBOUND function returns the maximum length of the array.
Recommended Articles
This has been a guide to VBA Split Function. Here we learned how to use VBA Split String Function in Excel along with some practical examples and downloadable excel template. Below are some useful excel articles related to VBA –
