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

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

1-select Visual Basic from Developer tab

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

2-Insert Module

Note: You must save the Excel file as Excel Macro-Enabled Workbook (*xlsm) to run the code.

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:
Split(expression, [delimiter], [limit], [compare])

3-syntax of SPLIT function

  • Arguments Explanation:
ARGUMENTS REQUIRED/OPTIONAL EXPLANATION
Expression Required The string to split based on the delimiter or space.
Delimiter Optional The character divides the string into parts.
Limit Optional Number of strings to be returned.
Compare Optional This argument is used for delimiters that are made up of one or letters. Check the Settings for Compare Argument section for more.
  • Settings for Compare Argument:
Constant Value Explanation
VbBinaryCompare 0 Performs a binary comparison.
vbTextCompare 1 Performs textual comparison.
vbDatabaseCompare 2 Performs comparison based on information on your database.

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.

4-vba code to split word from string

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

Code Breakdown

  • Here we set the name of the macro as Split_Word. Then, we declared four variables using the Dim All the variables are string types except variable i.
  • 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 maximum length of the string.
  • This loop helps us to store the substring in the VisualText Then, we added a MsgBox to return the output in a message box format.
  • After running the code, you will see the sub-strings in a message box.

5-splitted words in MsgBox

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

6-split word from text and store them in range

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
Here we have added the value into the Cells, where i+4 and 2 are the row and column numbers respectively.

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.

7-words separated with 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.

8-vba code to split words separated with comma delimiter

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
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 running the code, you will see the result below.

9-vba code to split words separated with comma delimiter

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.

10-dataset to divide address into parts

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

11-vba code to divide address into parts

'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

Within our code, we have inserted the Limit field in the SPLIT function 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.

  • After saving the code, type the formula below in Cell C5 and press Enter to see the result.
=ThreePartAddress(B5)
  • Further, drag the Fill Handle down to divide all the addresses into three parts.

12-dividing address into parts


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.

13-vba code to count words in a string

'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
Here, 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.
  • After saving the code, type the formula below in Cell C5 and press Enter to see the result.
=WordCount(B5)
  • Then, drag the Fill Handle down to apply the function to the rest of the cells.

14-user-defined function to count words using SPLIT function

Read More: How to Use VBA RTrim Function


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.

15-dataset to find specific word in a string

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.

16-vba code to find specific word in a string

'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

Code Breakdown

  • Here our function takes two parameters; the CRef (string to split) and the ValueNum (numerical position of the word to find).
  • 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 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 saving the code, insert the formula in Cell C5 and press Enter to see the result.
=FindSpecificWord(B5,3)
  • Then, drag the Fill Handle down to extract the city name from the other addresses.

17-user-defined function to find specific word


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.

18-changing limit parameter in SPLIT function

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
In the above code, we have highlighted the limit parameter in the third argument of the SPLIT function. We have set the limit parameter to 2. That means the code will return two substrings.
  • After running the code, you will see the two substrings in a message box.

19-output of changing limit parameter in SPLIT function

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

String Limit Output
“Apples,Bananas,Oranges,Mangoes” 1 Apples,Bananas,Oranges,Mangoes
“Apples,Bananas,Oranges,Mangoes” 2 Apples
Bananas,Oranges,Mangoes
“Apples,Bananas,Oranges,Mangoes” 3 Apples
Bananas
Oranges,Mangoes
“Apples,Bananas,Oranges,Mangoes” 4 Apples
Bananas
Oranges
Mangoes

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.

20-dataset to copy data using SPLIT function

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

21-vba code to copy data using SPLIT function

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
Here, myText variable contains information about an employee named Eliza Zheng. The details are separated with hyphen delimiters. The SPLIT function will split the string and store the substrings in the range B10:E10.
  • After running the code, you will get the following result.

22-output of copying data using SPLIT function


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.

23-dataset for using letter as delimiter inside SPLIT function

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

24-vba code to use letter as delimiter inside SPLIT function

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
Using this code, we have to insert the IDs inside the range B5:B10. The Text variable contains the IDs. Instead of delimiters, the letter “E” separates the IDs. To split the string into an array, we need to use the Compare parameter inside the SPLIT function.

Here, Split(Text, “E”, , vbTextCompare) contains four arguments. The last argument vbTextCompare helps to split the string by letter “E”.

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

25-output of using letter as delimiter inside SPLIT function

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

String Delimiter Type Result Remark
“35e31E43” e vbTextCompare 35
31
43
When we need to split by upper or lower case.
“35e31E43” e vbBinaryCompare 35
31E43
When we need to split by lowercase only.
Note: If you erase Compare argument, then the SPLIT function uses binary comparison by default.

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.

26-dataset for finding extension using SPLIT function

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

27-vba code to find extension using SPLIT function

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
This code runs a For Next loop in the range C5:C13. Inside the loop, we have used the SPLIT function. It splits the email addresses into two parts: first term of emails and the extensions and stores them in Column D and Column E respectively. After that, it hides Column D.
  • As a result, you will find the extensions and Column D with the first terms will be hidden.

28-result of finding extension


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

29-finding file names and extensions

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
This code is similar to the previous one. Here, we have inserted a ‘dot (.)’ in place of ‘at the rate (@)’ symbol inside the SPLIT function. Also, we erased the line to hide Column D.

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.

30-dataset for splitting words with multiple delimiters

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

31-vba code to split words with multiple delimiters

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
Here, we have used VBA REPLACE function to replace hyphen (-) with comma (,) delimiter. After that, we have used the SPLIT function to split the string into an array.
  • After running the code, range B10:E10 will store the string.

32-result of splitting words with multiple delimiters


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.

33- vba code for reversing split

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.

34-output of reversing split


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.

Download Practice Workbook

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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo