Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use VBA SPLIT Function in Excel (5 Examples)

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.

VBA SPLIT Function


Download Practice Workbook

You are welcome to download the practice workbook from the link below.

Sample content

Introduction to VBA SPLIT Function

Objective

The VBA SPLIT function in Excel splits the string into several substrings and returns a one-dimensional array of substrings.

Syntax

SPLIT( expression, [delimiter], [limit], [compare] )

Arguments Explanation

Arguments Required/Optional Explanation
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

– 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!

Step 1:

  • First of all, open a Module, to do that, firstly, from your Developer tab, go to,

Developer → Visual Basic

Split Words from String by Applying VBA SPLIT Function

  • 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

Step 2:

  • 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 
			
Code Explanation

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.

Split Words from String by Applying VBA SPLIT Function

  • 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.

Split Words from String by Applying VBA SPLIT Function

Step 3:

  • 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.

Split Words from String by Applying VBA SPLIT Function


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.

Apply VBA SPLIT Function For Splitting Words Separated with Multiple Delimiters

Now we will split words that are separated with multiple delimiters. Let’s follow the instructions below to learn!

Steps:

  • 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.

Apply VBA SPLIT Function For Splitting Words Separated with Multiple Delimiters


Similar Readings


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.

Divide into Multiple Parts by Using SPLIT Function in Excel VBA

We are set to divide these three parts into one each. Let’s follow the instructions below to learn!

Steps:

  • 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,
=ThreePartAddress(B5)
  • After that, we will find the address separated into three parts.

Divide into Multiple Parts by Using SPLIT Function in Excel VBA

  • Further, AutoFill the function to the rest of the cells in column C.

Divide into Multiple Parts by Using SPLIT Function in Excel VBA


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.

Count Words from String Using VBA SPLIT Function

We will find the total word of these sentences. We are going to create our own function using SPLIT.

Steps:

  • 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,
=ThreePartAddress(B5)
  • Hence, we have found the total number of words present in the sentence.

Count Words from String Using VBA SPLIT Function

  • 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.

Find Specific Word from a String into Array with Excel VBA

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!

Step 1:

  • 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,
=ThreePartAddress(B5)
  • 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.

Find Specific Word from a String into Array with Excel VBA


Things to Remember
  • 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


Conclusion

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.


Related Articles

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo