Method 1 – Use Split Function to Split String by Delimiter
Use the Split function to split strings using a delimiter. We have a string with Sales Rep names separated by a comma “,.” We want to split the names and store them in column B.
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.
Method 2 – Split Specific Times Using Limit Parameter
Using the limit parameter, we want to split the string at specific times according to our needs. For example, we have 5 products separated by a comma “,” and we want to split the string into the first 4 delimiters and 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.
Method 3 – Use Space As a Delimiter to Split Words from a Sentence in Multiple Columns
We will split string into multiple columns using space as a delimiter.
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).
- 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.
Method 4 – Excel VBA Split String by Delimiter Using Compare Parameter
Use VBA code to split strings by delimiter using a compare parameter. The compare parameter is “X”. This letter will behave like a delimiter. The Sales Rep names are separated by this uppercase “X”. 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.
Method 5 – Use Non-Printable Characters as Delimiter
If a non-printable character separates the content of a string, we can split the string by that character. The non-printable character is “vbCr”. We will use this as the delimiter and store the contents in the C column.
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.
Run the code and get your desired results.
Method 6 – Extract File Extension Using Delimiter
Extract the file extension from the file name using a delimiter. File extensions are separated by “.”. Use this character as the delimiter to split the file names from the extension. 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.
- The Split function splits the name of the current files using “.” as the delimiter to extract the extensions.
- The extensions are stored in column C.
After executing the code, the output will look like the following image.
Method 7 – Count Items in a Delimited String
Split strings and count the number of split items. 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. 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 displayed in MsgBox.
Method 8 – Split Address Using Delimiter
Split addresses as commas usually separate them. Use commas as delimiters. Write the following code in the module and then run it. The address will be separated and displayed in an 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.
Method 9 – Get State Name from a Address
Using a user-defined function, get the specific part of a string as output. Split the whole address and extract the state name as output. You 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.
The addresses are written where the state name is 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
A string contains various types of delimiters. Split the contents using multiple delimiters using VBA. We have an address that contains “–”, “,”. Use these characters as delimiters to split the address and store them in the worksheet. 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- The code initiates a loop, using 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.
- The code stores each of the substrings in different cells using a nested For loop.
You will get the output like the following image.
Things to Remember
- While using the 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.
