If you are looking for a way to split string by delimiter using VBA code in Excel, you have come to the right place. In Excel, you can easily split strings using any delimiters you want. In this article, we will walk you through 9 different examples to apply Excel VBA to split string by delimiter with proper illustrations.
How to Launch VBA Editor in Excel
To access the Microsoft Visual Basic window, go to the Developer tab and then click on Visual Basic. You can also open it by pressing Alt+F11 on your keyboard.
Then go to the Insert tab and click on Module to open the code module.
Excel VBA to Split String by Delimiter: 9 Suitable Examples
In this article, we will demonstrate how to apply Excel VBA split string by delimiter in 9 different examples.
1. Use Split Function to Split String by Delimiter
In this example, we will use the Split function to split strings by delimiter. We have a string with Sales Rep names separated by comma “,”. We want to split the names and store them in column B.
For this purpose, go to VBA code Module and write the following code there.
Sub SplitStringbyDelimiter()
Dim stringArray() As String, nameString As String, _
i As Variant, count As Integer
nameString = "John,Alex,Wendy,Gary,Claire, Drew"
stringArray = Split(nameString, ",")
For count = 0 To UBound(stringArray)
Range("B" & count + 5).Value = stringArray(count)
Next count
End Sub
Code Breakdown
nameString = "John, Alex ,Wendy, Gary, Claire, Drew"
stringArray = Split(nameString, ",")- A string variable nameString is defined and the Split function is called that will split nameString using “,”
For count = 0 To UBound(stringArray)
Range("B" & count + 5).Value = stringArray(count)
Next count- This part starts a loop and stores values obtained from stringArray in column B.
Press F5 on your keyboard to run the code and get your desired results.
2. Split Specific Times Using Limit Parameter
Now we will split the string a specific times as per our needs using the limit parameter. In this example, we have 5 products separated by a comma “,”. We want to split the string into the first 4 delimiters and then store them in the Product column.
You can use this VBA code to achieve this goal.
Sub SplitStringWithLimit()
Dim arrString() As String, strOriginal As String, _
varIndex As Variant, intCount As Integer
Dim ws As Worksheet
Set ws = ActiveSheet
strOriginal = "Keyboard, Printer, Scanner, Headset, Scanner"
arrString = Split(strOriginal, ",", 4)
ws.Range("C5").Resize(UBound(arrString) + 1, 1).ClearContents
For intCount = 0 To UBound(arrString)
ws.Range("C" & intCount + 5).Value = arrString(intCount)
Next intCount
End Sub
Code Breakdown
strOriginal = "Keyboard, Printer, Scanner, Headset, Scanner"
arrString = Split(strOriginal, ",", 4)- A string variable strOriginal is defined.
- The Split function returns an array of substrings obtained from splitting strOriginal.
- 4 is the maximum number of substrings returned by the Split function.
Run the code and get your desired output.
3. Use Space As Delimiter to Split Words from a Sentence in Multiple Columns
In this example, we will split string into multiple columns using space as a delimiter.
We will use the following code for this purpose.
Sub SplitinWords()
Dim TextStrng As String
Dim result() As String
Dim i As Integer
Dim j As Integer
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sentence")
For i = 5 To 10
TextStrng = ws.Cells(i, 2).Value
result() = Split(TextStrng)
For j = 0 To UBound(result)
ws.Cells(i, j + 3).Value = result(j)
Next j
Next i
End Sub
Code Breakdown
For i = 5 To 10
TextStrng = ws.Cells(i, 2).Value
result() = Split(TextStrng)
For j = 0 To UBound(result)
ws.Cells(i, j + 3).Value = result(j)
Next j
Next i- This code sets up a loop that iterates from 5 to 10.
- result() = Split(TextStrng) splits TextStrng using the default delimiter (Space).
- Then the code starts another loop to store each word of the TextStrng in different cells.
Once you run the above code, you will find that the words are stored in multiple columns.
4. Excel VBA Split String by Delimiter Using Compare Parameter
Now in Excel, we will use VBA code to split strings by delimiter using a compare parameter. For this example, the compare parameter is “X”. This letter will behave like a delimiter here. The Sales Rep names are separated by this uppercase “X”. We will split them using VBA code.
The VBA code is given below.
Sub SplitNames()
Dim NameArray() As String
Dim NamesString As String
Dim NameIndex As Integer
NamesString = "JohnXAlexXWendyXGaryXClaireXDrew"
NameArray = Split(NamesString, "X", , vbBinaryCompare)
For NameIndex = 0 To UBound(NameArray)
Range("B" & NameIndex + 5).Value = NameArray(NameIndex)
Next NameIndex
End Sub
Code Breakdown
NamesString = "JohnXAlexXWendyXGaryXClaireXDrew"
NameArray = Split(NamesString, "X", , vbBinaryCompare)- The first line assigns a string to the variable named NamesString.
- The Split function splits NamesString into substrings using the delimiter “X”.
- vbBinaryCompare specifies that comparison should be case-sensitive.
The names will be stored in the B column after executing the code.
5. Use Non-Printable Characters as Delimiter
If the content of a string is separated by a non-printable character, we can split the string by that character. In this example, the non-printable character is “vbCr”. We will use this as the delimiter and store the contents in the C column.
We will use the following code to do this.
Sub SplitbyNonPrintable()
Dim myArray() As String
Dim myString As String
Dim i As Variant
Dim n As Integer
myString = "Keyboard" & vbCr & "Printer" & vbCr & "Scanner" _
& vbCr & "Headset" & vbCr & "Scanner" & vbCr & "Printer"
myArray = Split(myString, vbCr, , vbTextCompare)
For n = 0 To UBound(myArray)
Range("C" & n + 5).Value = myArray(n)
Next n
End Sub
Code Breakdown
myString = "Keyboard" & vbCr & "Printer" & vbCr & "Scanner" _
& vbCr & "Headset" & vbCr & "Scanner" & vbCr & "Printer"
myArray = Split(myString, vbCr, , vbTextCompare)- The first line declares a string variable called myString and assigns its value.
- The substring values are separated by the vbCr constant, which is used as the delimiter.
- The vbCr constant represents a carriage return character.
Finally, run the code and get your desired results.
6. Extract File Extension Using Delimiter
You can also extract the file extension from the file name using a delimiter. File extensions are separated by “.”. Therefore, we will use this character as the delimiter to split the file names from the extension. Then we will store the extension in column C.
The VBA code is described below.
Sub FileExtension()
Dim myFiles As Variant
myFiles = Array(Range("B5").Value, Range("B6").Value, _
Range("B7").Value, Range("B8").Value)
Dim file As Variant
Dim arr() As String
For Each file In myFiles
arr = Split(file, ".")
Range("C" & Application.Match(file, _
myFiles, 0) + 4).Value = arr(UBound(arr))
Next file
End Sub
Code Breakdown
For Each file In myFiles
arr = Split(file, ".")
Range("C" & Application.Match(file, _
myFiles, 0) + 4).Value = arr(UBound(arr))
Next file- The code starts a loop that iterates through a collection of files referenced by the variable myFiles.
- Then the Split function splits the name of the current files using “.” as the delimiter to extract the extensions.
- After that it stores the extensions in column C.
After executing the code, the output will look like the following image.
7. Count Items in a Delimited String
In this example, we will split strings and count the number of split items. We will use the following code for this purpose.
Sub CountItems()
Dim itemList As String
itemList = "keyboard, Scanner, Printer, Headset, Mouse, Charger"
MsgBox "Number of items: " & UBound(Split(itemList, ",")) + 1
End SubPress F5 to run the code. As a result, a MsgBox will appear displaying the number of items.
Code Breakdown
itemList = "keyboard, Scanner, Printer, Headset, Mouse, Charger"
MsgBox "Number of items: " & UBound(Split(itemList, ",")) + 1- The first line declares a variable named itemList.
- The Split function splits the itemList into substrings.
- The UBound function returns the maximum index number of the array Split. Adding 1 to this number gives us the number of items which is displayed in MsgBox.
8. Split Address Using Delimiter
We can split addresses easily as they are separated by commas usually. We can simply use commas as delimiters to achieve this goal. Write the following code in the module and then run it. As a result, the address will be separated and displayed in a MsgBox.
Sub splitAddress()
Dim addressString As String
Dim addressParts() As String
Dim TextView As String
addressString = "211 E 3rd St, Austin, Texas, 78701"
addressParts = Split(addressString, ",", 4)
For i = LBound(addressParts) To UBound(addressParts)
TextView = TextView & addressParts(i) & vbNewLine
Next i
MsgBox TextView
End Sub
Code Breakdown
addressString = "211 E 3rd St, Austin, Texas, 78701"
addressParts = Split(addressString, ",", 4)- The code first declares a string variable named adressString.
- Then it splits addressString into substrings using the Split function.
For i = LBound(addressParts) To UBound(addressParts)
TextView = TextView & addressParts(i) & vbNewLine
Next i
MsgBox TextView- The substrings are then displayed in MsgBox in multiple lines.
9. Get State Name from a Address
Using a simple user-defined function, you can get the specific part of a string as output. In this example, we want to split the whole address and extract the state name as output. For this purpose, we will need the following UDF.
Function StateName(rng As Range, _
Element As Integer)
Dim State() As String
State = Split(rng, ",")
StateName = State(Element - 1)
End Function
Code Breakdown
State = Split(rng, ",")Uses the Split function to split the contents of the rng range using the comma (“,”) as the delimiter.
StateName = State(Element - 1)Retrieves the state name from the State array using the Element parameter as the index.
Here, the addresses are written where the state name is present in the 3rd position after the second comma. We can easily extract the state name by typing the following formula in a cell.
=StateName(B5,3)Excel VBA to Split by Multiple Delimiters
Sometimes a string contains various types of delimiters. We want to split the contents using multiple delimiters using VBA. In this example, we have an address that contains “–”, “,”. We will use both of these characters as delimiters to split the address and store them in the worksheet. We will use the following VBA code for this purpose.
Sub MultipleDelimiter()
Dim address As String
Dim parts() As String
Dim i As Integer
address = "2362-Orphan Road, Mountain Lake, Minnesota-56159"
parts = Split(address, "-")
Range("B5").Value = parts(0)
For i = 1 To UBound(parts)
If InStr(1, parts(i), ",") > 0 Then
Dim subParts() As String
subParts = Split(parts(i), ",")
For j = 0 To UBound(subParts)
Range("C5").Offset(0, j).Value = subParts(j)
Next j
Else
Range("F5").Value = parts(i)
End If
Next i
End Sub
Code Breakdown
address = "2362-Orphan Road, Mountain Lake, Minnesota-56159"
parts = Split(address, "-")
Range("B5").Value = parts(0)- The code splits the address into substrings using “-” as the delimiter.
- The substrings are stored in an array called parts. After this part is executed, the array will have three items (“2362”, “Orphan Road, Mountain Lake, Minnesota”, “56159”).
For i = 1 To UBound(parts)
If InStr(1, parts(i), ",") > 0 Then
Dim subParts() As String
subParts = Split(parts(i), ",")
For j = 0 To UBound(subParts)
Range("C5").Offset(0, j).Value = subParts(j)
Next j
Else
Range("F5").Value = parts(i)
End If
Next i- After that, the code initiates a loop, where it uses the Instr function to check if parts (i) contain “,”.
- If a comma is found, the code declares a new array called subParts to store the sub-parts obtained by splitting the current element using the Split function.
- Finally, the code stores each of the substrings in different cells using a nested For loop.
When you run the code, you will get the output like the following image.
Things to Remember
- While using compare parameter, you can use Option Compare Text to make the text case insensitive.
- Be careful about what character you are using as the delimiter.
Download Practice Workbook
Download this practice workbook to exercise while reading this article.
Conclusion
Thanks for making it this far. I hope you find this article helpful. In this article, we have demonstrated 9 examples of using Excel VBA to split string by delimiter. We have shown you how to use the Split function, how to split specific times, how to split a sentence etc. We have also covered using compare parameters, and non-printable characters as delimiters. Moreover, using delimiters to extract file extensions, count items, and split addresses is described in this article. If you have any queries or recommendations regarding this article, feel free to let us know in the comment section below.
Related Articles
- Excel VBA: Split String into Rows
- Excel VBA: Split String into Cells
- Excel VBA: Split String by Number of Characters
- How to Split a String into an Array in VBA
