Excel VBA to Split String by Delimiter: 9 Examples

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.

Use Split Function to Split String by Delimiter

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

VBA code Using Split Function to Split String by Delimiter in Excel

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.

Result after Using Split Function to Split String by Delimiter in Excel

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.

Split Specific Times Using Limit Parameter

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

Split Specific Times Using Limit Parameter

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.

Split Specific Times Using Limit Parameter

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 Space As Delimiter to Split Words from A Sentence in Multiple Columns

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

Use Space As Delimiter to Split Words from A Sentence in Multiple Columns

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.

Use Space As Delimiter to Split Words from A Sentence 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.

Excel VBA Split String by Delimiter Using Compare Parameter

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

Excel VBA Split String by Delimiter Using Compare Parameter

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.

Excel VBA Split String by Delimiter Using Compare Parameter

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 Non-Printable Character As Delimiter

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

Use Non-Printable Character As Delimiter

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.

Use Non-Printable Character As Delimiter

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.

Extract File Extension Using Delimiter

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

Extract File Extension Using Delimiter

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.

Extract File Extension Using Delimiter

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 Sub

Press F5 to run the code. A MsgBox will appear, displaying the number of items.

VBA code to Count Items in a Delimited String in Excel

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

Split Address Using Delimiter

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

VBA Code to Get State Name from Address by delimiter in Excel

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

VBA Split Multiple Delimiters

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.

VBA Split Multiple Delimiters

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.

Split String by Delimiter.xlsm

Get FREE Advanced Excel Exercises with Solutions!