How to Use VBA SPLIT Function in Excel (10 Ideal Examples)

Are you working with some strings and need to split them into multiple parts? Then, you are in the right place. When working in Excel, we may need to split a string of text into an array based on a delimiter or a space. For that purpose, we can use the VBA SPLIT function. The VBA SPLIT is a built-in function and can split a string of text based on a delimiter.

The overview video shows the simple use of the SPLIT function. Here, we have used the SPLIT function through a VBA code to split the text stored in Cell B4 into an array.

In practical life, we may need to divide an address into multiple parts, find a specific word or find the extension of an email or file name. In this article, we will show the use of the SPLIT function using different examples. So, without further delay, let’s start the discussion.

Download Practice Workbook

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

How to Launch VBA Editor in Excel

For launching this VBA Editor, you must need the Developer tab, now if you don’t see the Developer tab then you have to enable the Developer tab. After enabling the Developer tab, follow the steps below.

• To launch VBA Editor, select the Developer tab >> then Visual Basic.
• Alternatively, you can press Alt + F11 to open the Visual Basic Editor.

• Now, from the Insert tab >> select Module.

Introduction to VBA SPLIT Function in Excel

• Objective:

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

• Syntax:

• Arguments Explanation:

• Settings for Compare Argument:

The following are some examples where we showcase different VBA SPLIT applications in a practical setting.

10 Ideal Examples of Using VBA SPLIT Function in Excel

In the following sections, we will demonstrate 10 examples for describing the VBA SPLIT function. Hopefully, you will have a good knowledge of the SPLIT function after going through the examples.

1. Split Words from String Applying VBA SPLIT Function in Excel

The basic use of the SPLIT function is to split a string into substrings. Here, the VBA code will split the string, “We are learning VBA SPLIT Function” and show the sub-strings in a message box.

• For splitting words from string, insert the code below in the Module window.
• Press F5 key to run the code.

Sub Split_Word()
'Declaring Variables
Dim Text As String
Dim Result() As String
Dim VisualText As String
Dim i As Integer
'String to Split
Text = "We are learning VBA SPLIT Function."
Result = Split(Text)
'For loop for storing sub-string in VisualText variable
For i = LBound(Result()) To UBound(Result())
VisualText = VisualText & Result(i) & vbNewLine
Next i
'Returns the output in a message box
MsgBox VisualText
End Sub

• After running the code, you will see the sub-strings in a message box.

• 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.
• The code below splits the string and stores the sub-strings in the range B4:B9.

Sub Split_Word_from_Text()
'Declaring Variables
Dim Text As String
Dim Result() As String
Dim VisualText As String
Dim i As Integer
'String to split
Text = "We are learning VBA SPLIT Function."
Result = Split(Text)
'For loop for storing sub-string in range B4:B9
For i = LBound(Result()) To UBound(Result())
     Cells(i + 4, 2).Value = Result(i)
Next i
End Sub

Read More: Create Custom VBA functions and Use them in the Worksheet

2. Use VBA SPLIT Function for Splitting Words Separated with One Type of Delimiter

If there is no delimiter, then the SPLIT function assumes space as a delimiter.  A delimiter can be a comma, a semicolon, or any separating character. Here we are using a comma as the delimiter. We will split the words separated with the comma delimiter.

• To split words separated with the comma delimiter, type the code in the Module window.
• Hit the F5 key to run the code.

Sub Comma_Delimiter()
'Declaring Variables
Dim Text As String
Dim Result() As String
Dim i As Integer
'Words Separated with Comma Delimiter
Text = "Apples,Bananas,Oranges,Mangoes"
Result = Split(Text, ",")
'For loop to store the output in range B4:B7
For i = LBound(Result()) To UBound(Result())
     Cells(i + 4, 2).Value = Result(i)
Next i
End Sub

• After running the code, you will see the result below.

Read More: How to Use VBA Function Procedure with Arguments in Excel

3. Divide Address into Multiple Parts by Using SPLIT Function in Excel VBA

We can divide an address into multiple parts using the SPLIT function in Excel VBA. It seems a bit similar to splitting a 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.

• To divide the address into three parts, paste the following code in the Module window and save it by pressing Ctrl + S.

'User-defined function to divide address
Function ThreePartAddress(cellRef As Range)
'Declaring Variables
Dim TStrng As String
Dim Result() As String
Dim VisualText As String
Dim i As Integer
'Storing output in Result variable
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

• After saving the code, type the formula below in Cell C5 and press Enter to see the result.

• Further, drag the Fill Handle down to divide all the addresses into three parts.

Similar Readings

• How to Use VBA While Wend Statement in Excel (4 Examples)
  
• Use VBA UCASE function in Excel (4 Examples)
• How to Use InStr Function in VBA (3 Easy Examples)
  
• Use VBA LCase Function in Excel (3 Easy Examples)
• How to Return a Value in VBA Function (Both Array and Non-Array Values)

4. Count Words from String Using VBA SPLIT Function

The SPLIT function will be helpful to count the words in a string. Here, we will create a user-defined function to count the total words from a string using the SPLIT function.

• To create a function for counting words, paste the following code into the Module window and save it by pressing Ctrl + S.

'User-Defined Function to Count Words
Function WordCount(CRef As Range)
'Declaring Variables
Dim TextStrng As String
Dim Result() As String
'Using VBA TRIM function to trim the text and split it by space
Result = Split(WorksheetFunction.Trim(CRef.Text), " ")
'Adding 1 with the UBound of Result to get the word count
WordCount = UBound(Result()) + 1
End Function

• After saving the code, type the formula below in Cell C5 and press Enter to see the result.

• Then, drag the Fill Handle down to apply the function to the rest of the cells.

Read More: How to Use VBA RTrim Function (5 Suitable Examples)

5. Find a Specific Word from a String with VBA SPLIT Function

In this example, we will 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 will create a function named FindSpecificWord and retrieve the nth word from any string.

• To create the function, paste the following code in the Module window.
• Then, press Ctrl + S to save the code.

'User-Defined Function to find specific word
Function FindSpecificWord(CRef As Range, ValueNum As Integer)
Dim Result() As String
Result = Split(CRef, ",")
FindSpecificWord = Result(ValueNum - 1)
End Function

• After saving the code, insert the formula in Cell C5 and press Enter to see the result.

• Then, drag the Fill Handle down to extract the city name from the other addresses.

6. Change Limit Parameter in SPLIT Function to Determine Number of Substrings in Array

The Limit parameter generally indicates the number of substrings to be returned. Also, we can change the Limit parameter of the VBA SPLIT function to place items differently in an array.

Here, we will split the string “Apples,Bananas,Oranges,Mangoes” and show the result in a message box.

• To determine the number of substrings, type the following code in the Module window.
• Press F5 to run the code.

Sub Changing_Limit_Parameter()
'Declaring Variables
Dim myText As String
Dim Result() As String
Dim VisualText As String
Dim i As Integer
'Array to split
myText = "Apples,Bananas,Oranges,Mangoes"
'Limit is the third argument of SPLIT function
Result = Split(myText, ",", 2)
For i = LBound(Result()) To UBound(Result())
    VisualText = VisualText & Result(i) & vbNewLine
Next i
'This message box will display the result
MsgBox VisualText
End Sub

• After running the code, you will see the two substrings in a message box.

For different limits, you will get different results. We can show them in the following table:

Similar Readings

• How to Use VBA DateDiff Function in Excel (9 Examples)
• Random Number Excel Formula (5 Examples)
• How to Use VBA Str Function in Excel (4 Examples)
  
• Use VBA ChDir Function in Excel (4 Suitable Examples)
• How to Use VBA IsError Function (6 Examples)

7. Copy Data Using VBA SPLIT Function in Excel

We can also use the SPLIT function while copying data that are separated by delimiters. Here, we will insert an employee’s details in the range B10:E10.

• For inserting information about an employee, type the following code in the Module window.
• Hit the F5 key to run it.

Sub Copy_Split_Words()
Dim myText As String
'myText stores the text to split
myText = "E03519-Eliza Zheng-Computer Systems Manager-IT"
'This line will split myText
'Then,copy the sub-strings
'and paste them in the range B10:E10 of Copying Data worksheet
 Worksheets("Copying Data").Range("B10:E10").Value = Split(myText, "-")
End Sub

• After running the code, you will get the following result.

8. Use Letter as Delimiter in VBA SPLIT Function

In this example, we will use a letter as a delimiter and split the string into an array. Here, we will insert the IDs of some employees in the range B5:B10 from the string “E03611E04464E02135E01684E02968E03362”. We are using the letter “E” as a delimiter here.

We need to use the Compare parameter inside the VBA SPLIT function in this case.

• Type the following code in the Module window.
• Press F5 key to run it.

Sub Letter_as_Delimiter()
'Declaring Variables
Dim Text As String
Dim Result() As String
Dim VisualText As String
Dim i As Integer
'String to split
Text = "E03611E04464E02135E01684E02968E03362"
'Application of SPLIT function with letter E as delimeter
'While using letter as delimeter, we need to use Compare parameter
Result = Split(Text, "E", , vbTextCompare)
For i = LBound(Result()) To UBound(Result())
     Cells(i + 4, 2).Value = Result(i)
Next i
End Sub

• After running the code, we will see the IDs like the picture below.

Different compare arguments show different results. We can show it in the following table.

9. Find an Extension Using SPLIT Function in Excel

In Excel, we often need to find an extension of email addresses or files. Here, we have some email addresses and we will extract the first term of the emails and their extensions.

• To find the extensions of the email addresses, type the code in the Module window.
• Press F5 to run it.

Sub Finding_Extension()
'Decalaring Variables
Dim Text As String
Dim Result() As String
Dim i As Integer
'For loop to find extension and store the Result in Column E
For i = 1 To Range("C5:C13").Rows.Count
    Text = Range("C5").Cells(i)
    Result = Split(Text, "@")
    cell1 = Range("D" & 4 + i).Address
    cell2 = Range("E" & 4 + i).Address
    Range(cell1, cell2).Value = Result
Next i
'After storing the result, this line will hide Column D
Range("D5").EntireColumn.Hidden = True
End Sub

• As a result, you will find the extensions and Column D with the first terms will be hidden.

What to Do If You Need to Find File Names and Their Extensions?

To find file names and their extensions, you can use the following VBA code. Suppose, you have a file name, “My Resume.pdf”. The code will extract the file name “My Resume” and its extension “.pdf”.

Sub Finding_Extension()
'Decalaring Variables
Dim Text As String
Dim Result() As String
Dim i As Integer
'For loop to find file names and extensions
For i = 1 To Range("C5:C13").Rows.Count
    Text = Range("C5").Cells(i)
    Result = Split(Text, ".")
    cell1 = Range("D" & 4 + i).Address
    cell2 = Range("E" & 4 + i).Address
    Range(cell1, cell2).Value = Result
Next i
End Sub

10. Apply VBA SPLIT Function for Splitting Words Separated with Multiple Delimiters

In Example 2, we show the way of splitting words separated with one type of delimiter. We can also use the SPLIT function to split words with multiple delimiters. For example, the string “E03519,Eliza Zheng-Computer Systems Manager,IT” contains comma (,) and hyphen (-) as delimiters. We will split this string and insert the array in range B10:E10.

• To split the string separated with multiple delimiters, type the code in the Module window.
• Hit the F5 key to run the code.

Sub Multiple_Delimiters()
'Decalaring Variables
Dim myText As String
'String with multiple delimiters
myText = "E03519,Eliza Zheng-Computer Systems Manager,IT"
'This line replaces hyphen (-) with comma (,)
myNewText = Replace(myText, "-", ",")
'This line splits the text stored in myNewText variable
FinalText = Split(myNewText, ",")
'Range B10:E10 stores the result
Range("B10:E10").Value = FinalText
End Sub

• After running the code, range B10:E10 will store the string.

How to Use JOIN Function to Reverse a Split in Excel VBA

Till now, we saw examples of splitting a string into an array. But what if we need to reverse a split? We can use the VBA JOIN function in that case.

To explain reverse split, we will use the string “We,are,learning,reverse,split,in,Excel” inside the code. The comma (,) delimiter separates it. We will place the string in Cell B4. After that, we will split it into an array. Finally, join the sub-strings using space as a delimiter and place them in Cell B6.

• To understand the use of the JOIN function for reversing split, type the code in the Module window.
• Press F5 key to run it.

Sub Join_to_Reverse_Split()
'Declare variables
Dim myArr() As String, myStr As String
Dim Trgt As String
'String to split
myStr = "We,are,learning,reverse,split,in,Excel"
'Place the string at Cell B4
Range("B4").Value = myStr
'Use Split function to split the string where comma delimiters are placed
myArr = Split(myStr, ",")
'Use Join function to re-create the original string using a space as delimiter
Trgt = Join(myArr, " ")
'Place the result string at Cell B6
Range("B6").Value = Trgt
End Sub

• After running the code, you will get the following result.

Things to Remember

• The SPLIT function assumes space as the delimiter if you don’t provide the delimiter.
• One must specify the delimiter inside the SPLIT function if the delimiter is not a space.
• The UBOUND function finds the maximum length and the LBOUND function finds the minimum length of the array.

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

• How to Use VBA Forecast Function in Excel (3 Ideal Examples)
  
• Call a Sub in VBA in Excel (4 Examples)
  
• How to Create a Body Mass Index Calculator in Excel Using VBA
• Use VBA Asc Function (5 Practical Examples)
  
• How to Use VBA Large Function in Excel (4 Suitable Examples)
• Use VBA StrComp in Excel (5 Common Examples)
• How to Use IsNull Function in Excel VBA (5 Examples)