Contents
- 1 Introduction
- 2 Glossary
- 3 Split Syntax
- 4 Split Return Value
- 5 Split Function Parameters
- 6 VBA Split – Simple Example
- 7 Split Limit Parameter
- 8 Split Compare Parameter
- 9 Reversing Split
- 10 Split Example – Names
- 11 Split Example – Filenames
- 12 Split Example – Copy to a Range
- 13 Split Example – Count Items
- 14 Split Example – Using Join
- 15 Further Reading
- 16 What’s Next?
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”
- If we want to split by x when lower case only – then we use vbBinaryCompare.
- If we want to split by upper or lower case x – then we use vbTextCompare.
- 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
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.)
Paul,
In your last coding, can you explain this line….
‘ Add one to the number
arr(2) = CStr(CLng(arr(2)) + 1)
What are CStr and Clng?
Hi Richard,
Actually CStr and CLng are not needed in this case. I have updated the code to:
arr(2) = arr(2) + 1
CStr converts to a string and CLng convert to a long. In many scenarios, VBA will automatically convert the variable to the correct type.
Paul
Hi Paul.
I am always impressed by the clarity and unusual simplicity as you expose the knowledge.
Have a great weekend.
Thanks João
Hi Paul ,
great articles, i tried the last example for myself and instead using ubound and lbound i used for each syntax. It did the same work but didn´t updated original array. It is this reason why You used Lbound x Ubound syntax ? Is also possible in my solution update the original array? And why it didn´t update it for me?
Sorry for my english
My solution:
For Each nummer In myNummers
arr = Split(nummer, “.”)
arr(2) = arr(2) + 1
nummer = Join(arr, “.”)
Debug.Print nummer
Next nummer
Hi Vladimir,
You cannot update the array using the For Each variable. The variable nummer is for reading only.
Silly me – I used a bad (html) example
My comment should read
Hi Paul
For some situations it may be useful to know the delimiter may be more than one character eg ^G^
#justsaying
Split can handle multiple characters as the delimeter.
From an admirer of your coding skills, accept some minor improvements to the following code:
Sub Instr_Firstname()
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 John
Debug.Print Right(s, length – position)
End Sub
Propositions
1 The name should rather read as “Sub Instr_lastname()”
2 To avoid unnecessary spaces the line “Debug.Print Right(s, length – position)” should be
“Debug.Print trim(Right(s, length – position))
Is there any way to split a text under two conditions? For example: “cat dog toy” can be split by space and I also have “cat/dog toy”. How can split under this two conditions? Both must give me cat, dog, toy as a result
You can do it like this:
I’m parsing short pieces of text that come from multiple sources out of my control and I often find more than one space between the words, so I end up with arrays full of empty cells. I cater for this by looping through the array to re-creating the text with one space between each word then re-splitting it. Not very pretty. Do you have an elegant solution for this scenario?
Great work by the way. Really appreciate your efforts.
If the variable you want to split is named ‘Sample’ and you want to spilt it into an array named ‘ArrayOfSamples’ , then you should include a Worksheetfunction.Trim() function. Trim removes all leading and trailing spaces, and it crushes multiple adjacent spaces into a single space.
Use like this:
Sample = WorksheetFunction.Trim(Sample)
ArrayOfSamples = Split( Sample, ” ” )
Or like this:
ArrayOfSamples = Split( WorksheetFunction.Trim(Sample), ” ” )
Note that there is a small but critical difference between VBA function named Trim() and the WorksheetFunction names Trim(). We use the latter version here.
How can I convert an array of type variant such as:
Dim coll As Object
Set coll = CreateObject(“System.Collections.ArrayList”)
coll.Add “248, 0, 0, 0, 14, 31, 186, 14, 0, 180, 9”
to an Array where each element is the data between the commas, rather than the entire string being an element itself? Keep in mind I cannot use a simple array of individual elements such as:
buf = Array(248, 0, 0, 0, 14, 31, 186, 14, 0, 180, 9) because this line would be too long
If you are using an ArrayList then you need to add each item individually.
So you would split the string to an array and then read through the array and add each item.
Hello Paul,
Is there any way to split a number under two conditions?
For example:
I used VBA macro and I have “10,15” in a cell/column also how can I get the “1” can be split by comma. How can split under this two conditions?