Excel VBA to Split String by Delimiter (9 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel vba split string by delimiter


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.

Go to Microsoft Visual Basic Application

Then go to the Insert tab and click on Module to open the code module.

Insert a code Module in VBA


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.

Use Split Function to Split String by Delimiter

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

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


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.

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


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.

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

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

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).
  • 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.

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

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


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.

Use Non-Printable Character As Delimiter

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

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.

Finally, run the code and get your desired results.

Use Non-Printable Character As Delimiter


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.

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.
  • 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.

Extract File Extension Using Delimiter


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 Sub

Press F5 to run the code. As a result, 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 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

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.


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

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.

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

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
  • 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.

VBA Split Multiple Delimiters


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alif Bin Hussain
Alif Bin Hussain

Alif Bin Hussain earned a BSc in Civil Engineering from Bangladesh University of Engineering and Technology. As an engineering graduate, he has a deep passion for research and innovation. He loves to play with Excel. In his role as an Excel & VBA Content Developer at ExcelDemy, he not only solves tricky problems but also shows enthusiasm and expertise in handling tough situations with finesse, emphasizing his dedication to delivering top-notch content. He is interested in C, C++,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo