VBA Split Function

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What is VBA Split Function in Excel?

The Split function in VBA is a very useful string function that one can use to split strings into multiple substrings based on a delimiter provided to the function and a comparison method. Of course, there are other string functions, too, which convert a string into a substring. But, the Split function can split a string into more than one substring.

In a normal worksheet, the LEFT function, MID functionsMID FunctionsThe mid function in Excel is a text function that finds strings and returns them from any mid-part of the spreadsheet. read more, and RIGHT in excelRIGHT In ExcelRight function is a text function which gives the number of characters from the end from the string which is from right to left. For example, if we use this function as =RIGHT ( “ANAND”,2) this will give us ND as the result.read more are used as text functionsText Functions In ExcelTEXT function in excel is a string function used to change a given input to the text provided in a specified number format. It is used when we large data sets from multiple users and the formats are different.read more to extract the portion of the sentence. For example, extraction of first, middle, and last names is the common scenario we have seen. But in VBA, we have the more versatile SPLIT function, which will do a similar job for you. SPLIT is a built-in function in Excel VBA that can split the supplied sentence based on the delimiter. So, for example, if you want to split the email address into different parts, the common element in the email address is “@” in all the email IDs, so “@” becomes a delimiter here.

VBA Split Function

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Split Function (wallstreetmojo.com)

VBA Split String Function

Like all other functions, split has its syntax. For example, below are the Excel VBA Split string function parameters.

Split Formula
  • Value or Expression: This is nothing but the actual value we were trying to split. For example, if you want to split first and last names, the full name is the value here.
  • [Delimiter]: What is the common element to split the Value or Expression? Email ID’s “@” is the common element, and address comma (,) is the common element. If you ignore this, it considers the space character as the default value.
  • [Limit]: How many substrings do 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,” and “is Excel.”
  • [Compare]: Since we do not use the 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.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Examples of VBA Split String Function

Below are the practical examples of the Split function in Excel VBA.

You can download this VBA Split Function Excel Template here – VBA Split Function Excel Template

Example #1 – Split the Sentence

The Split function returns the result in the array, which will start from 0. All the arrays start from 0, not from 1.

Assume you have the word “My Name is Excel VBA” in cell A1.

VBA Split Example 1

Now, you want to split this sentence into pieces like “My,” “Name,” “is,” “Excel,” and “VBA.” Then, we can return this result using the Excel VBA SPLIT String function.

Step 1: Start the Macro with the name.

VBA Split Example 1-1

Code:

Sub Split_Example1()

End Sub

Step 2: Declare three variables.

VBA Split Example 1-2

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

VBA Split Example 1-3

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, apply the VBA Split String function for the “My Result” variable.

VBA Split Example 1-4

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 the variable “MyText,” enter this argument here.

VBA Split Example 1-5

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 of now, ignore all the other parameters.

Step 6: Now, “My Result” holds this split result. As we told earlier in the post, the 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 impact starting the SPLIT function, we can use this code.

Example #2 – VBA SPLIT String with UBOUND Function

To store the result of the SPLIT function, we can use the vba UBOUND functionVba UBOUND FunctionUBOUND, also known as Upper Bound, is a VBA function that is used in conjunction with its opposite function, LBOUND, also known as Lower Bound. This function is used to determine the length of an array in a code, and as the name suggests, UBOUND is used to define the array's upper limit.read more along with the SPLIT function.

The UBOUND function will return 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 us split this word and store it from cell A1 onwards.

Step 1: Let us continue from where we left off in the previous example.

VBA Split Example 2

Step 2: Now, apply FOR NEXT LOOP in VBAFOR NEXT LOOP In VBAAll programming languages make use of the VBA For Next loop. After the FOR statement, there is a criterion in this loop, and the code loops until the criteria are reached. read more from 0 to the maximum length of the array, UBOUND.

VBA Split Example 2-1

We started from zero because SPLIT will store the result from zero, not from 1.

Step 3: Now, apply the VBA CELLS propertyVBA CELLS PropertyCells are cells of the worksheet, and in VBA, when we refer to cells as a range property, we refer to the same cells. In VBA concepts, cells are also the same, no different from normal excel cells.read more and store the result.

Code:

Cells(i + 1, 1).Value = MyResult(i)
Example 2-2

Step 4: Run this code. We would have split values.

Example 2-3

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.

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
Return Word Count 1

Please copy and paste the above VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more and run it. The message box will return the result.

Return Word Count 2

Things to Remember

  • The SPLIT function automatically thinks of the delimiter as space if one does not supply the delimiter.
  • To split except space, you must specify the delimiter in double quotes.
  • The SPLIT function stores the result as array results.
  • The UBOUND function returns the maximum length of the array.

This article has been a guide to VBA Split Function. Here, we learned how to use Excel’s VBA Split String function, with some practical examples and a downloadable Excel template. Below are useful Excel articles related to VBA: –