Introduction

The VBA Split Function is used is to split a string of text into an array. The text is split based on a given delimiter – e.g. a comma, space, colon etc.

For example, imagine we have the following string:

“Apple:Orange:Pear:Plum”

You can see that each item separated by the colon sign. We call the colon sign the delimiter.

 

We can split this string into an array:

' https://excelmacromastery.com/
Sub SplitBasic()

    Dim arr() As String
    arr = Split("John:Jane:Paul:Sophie", ":")

End Sub

 

Once it is in an array it is easy to access each item:

 

Glossary

Array – a structure for storing a group of similar variables.

Ubound – this function gives the last position of an array.

Lbound – this function gives the first position of an array. For an array, returned by the Split function, the first position is zero.

Instr – this function is used to search for a string within a string and return the position.

InStrRev – the same as Instr but searches a string from the end.

 

Split Syntax

Split expression, delimiter[optional], limit[optional], compare[optional]

 

Split Return Value

The Split function returns an array.

 

 Split Function Parameters

expression – this is the text string that is to be split.

delimiter [optional] – the character delimiter used in the string to separate each item. If you don’t use this parameter then VBA uses space as the delimiter.

limit [optional] – this allows us to set the number of items in the result. If we use 1 as the limit then no split takes place and the full string is returned.

compare [optional] – if we are using letters as the delimiter then this determines if we take the case of letters into consideration.

 

VBA Split – Simple Example

The following code shows an example of using the Split function and printing the result to the Immediate Window:

' https://excelmacromastery.com/
Sub VBA_Split_Print()

    Dim arr() As String
    ' Split the string to an array
    arr = Split("John:Jane:Paul:Sophie", ":")

    ' Print each item in the array to the Immediate Window(Ctrl + G)
    Dim name As Variant
    For Each name In arr
        Debug.Print name
    Next

End Sub

Output
John
Jane
Paul
Sophie

 

When we split the string into an array we have an item in each position in the array. This means we can easily access any item using the array position:

' https://excelmacromastery.com/
Sub VBA_Split_PrintArr()

    Dim arr() As String
    ' Split the string to an array
    arr = Split("John:Jane:Paul:Sophie", ":")

    Debug.Print arr(0) ' Print John
    Debug.Print arr(1) ' Print Jane
    Debug.Print arr(2) ' Print Paul
    Debug.Print arr(3) ' Print Sophie

End Sub

 

Split returns an array that starts at position zero. If we want to use a For statement to read through the array we can use LBound and UBound to give us the first and last positions of the array:

' https://excelmacromastery.com/
Sub VBA_Split_Print()

    Dim arr() As String
    ' Split the string to an array
    arr = Split("John:Jane:Paul:Sophie", ":")

    ' Print each item in the array to the Immediate Window(Ctrl + G)
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        Debug.Print arr(i)
    Next

End Sub

 

Split Limit Parameter

The Limit parameter is used to determine how items are placed in the array. In other words, how many items is the original string split into.

The table below shows the results of using different limits this sample string:

String	Limit	Result
"John:Jane:Paul:Sophie"	1	John:Jane:Paul:Sophie
"John:Jane:Paul:Sophie"	2	John Jane:Paul:Sophie
"John:Jane:Paul:Sophie"	3	John Jane Paul:Sophie
"John:Jane:Paul:Sophie"	4	John Jane Paul Sophie

 

You can try out the code for yourself:

' https://excelmacromastery.com/
Sub VBA_Split_Limit()

    Dim arr() As String
    ' Split the string to an array
    arr = Split("John:Jane:Paul:Sophie", ":", 1)

    ' Print each item in the array to the Immediate Window(Ctrl + G)
    Dim name As Variant
    For Each name In arr
        Debug.Print name
    Next

End Sub

 

Split Compare Parameter

The Compare parameter is used for delimiters that are made up of one or letters.

For example, imagine we want to use the letter x as a delimiter in the following string:

“12x34X45x”

1. If we want to split by x when lower case only – then we use vbBinaryCompare.
2. If we want to split by upper or lower case x – then we use vbTextCompare.
3. vbUseCompareOption is the default and tells split to use the module Compare settings. Read more about the module compare settings here.

The following code shows how we use the Compare parameter:

' https://excelmacromastery.com/
Sub VBA_Split_Compare()

    Dim arr() As String
    ' Split the string to an array - not case sensitive
    arr = Split("12x34X45", "x", , vbTextCompare)

    ' Print each item in the array to the Immediate Window(Ctrl + G)
    Dim name As Variant
    For Each name In arr
        Debug.Print name
    Next

End Sub

 

The following table shows the results from the different Compare arguments:

String	Delimiter	Compare Type	Result
"12x34X45"	x	vbCompareText	12 34 45
"12x34X45"	x	vbCompareBinary	12 34X45

Reversing Split

We can use the Join function to do the opposite of what the split function does. Join converts an array into a string and adds a given delimiter.

This can be useful as sometimes we may want to split a string, update one or more values in the array and then convert the array to a string.

This example shows how to use Join:

' https://excelmacromastery.com/
Sub UsingJoin()

    Dim arr As Variant
    
    ' create an array using the array function
    arr = Array("Apple", "Orange", "Pear")
    
    Dim s As String
    ' Covert the array to a string using the colon delimiter
    s = Join(arr, ":")
    
    ' Print the string to the Immediate Window(Ctrl + G)
    Debug.Print s

End Sub

 

See the section “Split Example – Using Join” for an example of using the Join function with the Split function.

 

Split Example – Names

A really great example of Split is when dealing with names.

Imagine we have the name “John Henry Smith” and we want to extract each name.

We can use Left and Instr to get the first name:

' https://excelmacromastery.com/
Sub Instr_Firstname()
    
    Dim s As String
    s = "John Henry Smith"
    
    ' Get the position of the first space
    Dim position As Long
    position = InStr(s, " ") - 1

    ' Prints John
    Debug.Print Left(s, position)

End Sub

To get the last name is a bit trickier:

' https://excelmacromastery.com/
Sub Instr_Lastname()
    
    Dim s As String
    s = "John Henry Smith"
    
    ' Get the position of the last space
    Dim position As Long, length As Long
    position = InStrRev(s, " ") - 1
    length = Len(s)
    
    ' Prints Smith
    Debug.Print Right(s, length - position)

End Sub

 

Getting names that are not in the first or last position gets very messy indeed. However,  using Split we can simplify the whole process:

' https://excelmacromastery.com/
Sub SplitName()

    Dim s As String: s = "John Henry Smith"
    
    Dim arr() As String
    
    arr = Split(s, " ")
    Debug.Print arr(0) ' John
    Debug.Print arr(1) ' Henry
    Debug.Print arr(2) ' Smith

End Sub

 

We actually don’t need to use an array as we can see in the next example. It is not efficient to call the Split function 3 times instead of 1 but it does look nice in this example:

' https://excelmacromastery.com/
Sub SplitName()

    Dim s As String: s = "John Henry Smith"
    
    Debug.Print Split(s, " ")(0) ' John
    Debug.Print Split(s, " ")(1) ' Henry
    Debug.Print Split(s, " ")(2) ' Smith

End Sub

 

Split Example – Filenames

In the next example we use Split to get the extension part of a filename:

' https://excelmacromastery.com/
Sub GetFileExt()

    ' Create an array of filenames for our test
    Dim myFiles As Variant
    myFiles = Array("my resume.xlsx", "myresume2.doc", "my resume latest ver.pdf")

    Dim file As Variant, arr() As String
    ' Read through the filenames
    For Each file In myFiles
        ' Split by the period
        arr = Split(file, ".")
        ' Use Ubound to get the last position in the array
        Debug.Print arr(UBound(arr))
    Next file

End Sub

 

Here is an interesting one you can try for yourself. Given a full file name, try to write code to extract the filename without the extension or folder.

For example for “C:\MyDocs\Jan\MyResume.Doc” we want to extract MyResume.

' https://excelmacromastery.com/
Sub GetFilenamePart()

    ' Create an array of filenames for our test
    Dim myFiles As Variant
    myFiles = Array("C:\MyDocs\Jan\MyResume.Doc" _
                , "C:\MyMusic\Songs\lovesong.mp3" _
                , "D:\MyGames\Games\Saved\savedbattle.sav")

    Dim file As Variant, arr() As String
    ' Read through the filenames
    For Each file In myFiles
        
        ' Split by the period
        arr = Split(file, ".")
        
        ' Split by the folder separator /
        arr = Split(arr(0), Application.PathSeparator)
        
        Debug.Print arr(UBound(arr))
    
    Next file

End Sub

Output

MyResume
lovesong
savedbattle

 

Split Example – Copy to a Range

Because the result of Split is an array, we easily copy it to a range.

' https://excelmacromastery.com/
Sub VBA_Split_Range()

    Dim s As String
    s = "001,John Smith,New York,067435334"
    
    ' write the values to cells A1 to D1
    Sheet1.Range("A1:D1").Value = Split(s, ",")
    
    ' write the values to cells A1 to A4
    Sheet1.Range("A1:A4").Value = WorksheetFunction.Transpose(Split(s, ","))

End Sub

 

Split Example – Count Items

If we want to count the number of items in a delimited string we can use Split to do this for us.

We simply split the string and then use the Ubound function to give us back the number of items. We saw already that UBound is used to give us back the last position in an array. Because, the array start at zero, we need to add one to get the number of items.

' https://excelmacromastery.com/
Sub Split_Count()
    
    Dim s As String
    s = "Apple,Orange,Mango,Peach,Plum,Banana,Pear"

    Debug.Print "number of items:" & UBound(Split(s, ",")) + 1

End Sub

 

Split Example – Using Join

This is an interesting one that you may want to try yourself. Take the three strings below:

123.3456.96.345
1234.1156.7.345
1273.9998.123.345

We want to add one to the third number in each string. For example, the first string should become  123.3456.97.345.

Before you try yourself, I will give one hint. You can use the Join function to reverse the Split operation. It takes a array and delimiter and creates a string.

You can start with this code:

' https://excelmacromastery.com/
Sub UpdateNumber()

    ' Create an array of number for our test
    Dim myNums As Variant
    myNums = Array("123.3456.99.345" _
                , "1234.1156.7.98" _
                , "1273.9998.123.3235")

    ' Read through the strings
    Dim i As Long, arr() As String
    For i = LBound(myNums) To UBound(myNums)
        
        ' add your code here

    Next i
    
    ' Print the updated array to the Immediate Window(Ctrl+G)
    Debug.Print vbNewLine & "Printing new array"
    For i = LBound(myNums) To UBound(myNums)
        Debug.Print myNums(i)
    Next i

End Sub

 

This is how to do it:

' https://excelmacromastery.com/
Sub UpdateNumber()

    ' Create an array for our test
    Dim myNums As Variant
    myNums = Array("123.3456.99.345" _
                , "1234.1156.7.98" _
                , "1273.9998.123.3235")

    ' Read through the strings
    Dim i As Long, arr() As String
    For i = LBound(myNums) To UBound(myNums)
        
        ' Split the string to an array
        arr = Split(myNums(i), ".")
        
        ' Add one to the number
        arr(2) = arr(2) + 1
        
        ' convert the array back to a string
        myNums(i) = Join(arr, ".")

    Next i
    
    ' Print the updated array to the Immediate Window(Ctrl+G)
    Debug.Print vbNewLine & "Printing new array"
    For i = LBound(myNums) To UBound(myNums)
        Debug.Print myNums(i)
    Next i

End Sub

 Output

123.3456.100.345
1234.1156.8.345
1273.9998.124.345

 

Further Reading

The Ultimate Guide to VBA String Functions

Extracting using the Split function

VBA Arrays

VBA For Loop

Microsoft Docs – Split function

 

If you would like to see some real-world examples of using Split, you will find them in the post How to Easily Extract From Any String Without Using VBA InStr.

 

What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars and all the tutorials.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)