VBA to Split with Multiple Delimiters in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Have you ever used VBA to Split strings with Multiple Delimiters in Excel? VBA automates the tasks and processes in Excel. Suppose, you have a long text string that is separated by different delimiters. You want to split them into different columns according to the delimiters. There are different Excel functions to do that. But if you want to do it automatically with one click, then you have to use VBA. Go through the article to get the proper insight on how to use VBA to split strings with multiple delimiters. So, let’s get started.

This is an overview of our work. You will get a primary idea from this on how to split multiple delimiters using Excel VBA.

Overview of VBA to split string with multiple delimiters


How to Launch VBA Editor in Excel

You can launch the VBA editor by following these simple methods. Here, we use the developer tab to insert the module where we write the code. Follow the simple steps to open the VBA editor.

  • Initially, hover over to the Developer tab >> choose Visual Basic.

Launching Visual Basic Editor

Note: By default, the Developer tab remains hidden. In that case, you have to enable the Developer tab.

  • Then, the Visual Basic Editor launches and goes to the Insert tab >> Module >> Module1.

Inserting Module


VBA to Split with Multiple Delimiters in Excel: 2 Easy Ways

We have taken a dataset of “Student Information of ABC College”. Where the Name, ID, and Department Names are separated with delimiters. We put a comma (,) and a hyphen (-) here. Now, we will split the string into multiple columns for multiple delimiters with VBA. Here, VBA is used to split multiple delimiters.

Sample dataset

Not to mention, we have used the Microsoft 365 version. You may use any other version at your discretion.


1. Combining VBA Replace & Split Functions with For Loop

In this method, you’ll see the combination of two specific VBA functions with a For Loop. Firstly, you have to use the VBA Replace function to replace diverse delimiters with a specific delimiter. Then, you have to use the VBA Split function to split the strings based on the specific delimiter. We have demonstrated two different cases here for better understanding.


1.1 Split Array of Strings Based on Array of Multiple Delimiters

In the first case, we used the Split and Replace functions to split string into cells. The Replace function replaces a specific character with other characters. Then the Split function separated the values with the specific delimiters that we have inputted in the variables. The split value is then displayed in the Immediate window. We have attached the VBA code below.

Sub SplitUsingSplitWithLastDelimiter()
    Dim str As String
    Dim delims As Variant
    Dim words() As String
    Dim i As Long
    str = "John,Peter;Max-Thomas!Williams-Lewis"
    delims = Array(",", ";", "-", "!")
    ' Replace the delimiters with spaces
    For i = 0 To UBound(delims)
        str = Replace(str, delims(i), " ")
    Next i
    ' Split the string using spaces
    words = Split(str, " ")
    ' Add the original delimiter back to each resulting substring
    For i = 0 To UBound(words)
        For j = 0 To UBound(delims)
            If Right(words(i), 1) = " " Then
                words(i) = Left(words(i), Len(words(i)) - 1)
            End If
            If InStr(words(i), " ") > 0 Then
                words(i) = Replace(words(i), " ", delims(j), , 1)
            End If
        Next j
    Next i
    ' Print the resulting words
    For i = 0 To UBound(words)
        Debug.Print words(i)
    Next i
End Sub

VBA code to split multiple delimiters using Split function

Code Breakdown:

  • The above code indicates a sub-procedure called “SplitUsingSplitWithLastDelimiter()” that separates a string into substrings based on a set of delimiters and then re-adds the original delimiter into the resulting strings.
  • A few variables are first declared in the code: “str,” a string variable that will hold the resultant substrings, “delims,” a variant array variable that will hold the set of delimiters, “words,” a string array variable, and two long variables, “i” and “j,” which will be used as counters in the For Loops.
  • Next, the code sets the value of “str” to a sample string that contains multiple delimiters, including commas, semicolons, dashes, and exclamation points. The code then uses a for-loop to replace each delimiter in “str” with a space character.
  • After that, the code uses the “Split” function to split the modified string into an array of substrings based on spaces. These substrings are stored in the “words” array.
  • Finally, the code uses two nested for-loops to iterate through each substring in the “words” array and add the original delimiter back to the end of each substring. The resulting substrings are printed to the debug window using the “Debug.Print” statement.

Final output after splitting the string in the immediate window


1.2 Split Strings in a Cell Range Based on Multiple Delimiters

In this case, we also use the combination of the VBA Split and Replace function with a For Loop, but based on the practical dataset available in the worksheet. You can see in the below dataset where we will split the value of column B, which has two delimiters in columns C, D, and E.

Dataset of splitting strings using VBA functions

Sub SplitString()
    Dim SV() As String
    Dim i As Integer
    Dim j As Integer
    For i = 5 To 12
    NS = Replace(Range("B" & i), ",", "-")
    SV = Split(NS, "-")
    For j = 1 To 3
        Cells(i, j + 2).Value = SV(j - 1)
        Next j
    Next i
End Sub

VBA code to split multiple delimiters using Replace and Split functions combinedly

Code Breakdown:

  • The above code begins with variables such as “SV” and “i“, and “ j“.
  • Then we use a for loop to iterate the value from rows 5 to 12 in the worksheet.
  • In the iteration process, we used the Replace function to replace any commas “,” in column B with hyphens ““. This result is stored in a variable called “NS“.
  • After that, the code uses the Split function to split the data carrying hyphens.
  • Finally, the outer for-loop continues to the next row until it reaches row 12.

Run the code with the F5 key and get the below output. You can see the data from column B has been split into columns C, D, and E.

Final output after splitting the value

Read More: Excel VBA to Split String by Delimiter


2. Using VBA InStr Function with Do While Loop

In this method, we used the VBA InStr function and the VBA Mid function. The InStr function finds the first occurrence of the delimiters and inserts the substrings in a new variable, and then the result is separated with the Mid function. Follow the below code for better visualization.

Sub SplitUsingInstr()
    Dim str As String
    Dim delims As Variant
    Dim words() As String
    Dim i As Long, j As Long
    Dim pos As Long, start As Long, p As Long
    str = "John,Peter;Max-Thomas!Williams-Lewis"
    delims = Array(",", ";", "-", "!")
    ReDim words(0)
    start = 1
    p = Len(str)
    Do While start <= p
        ' Find the position of the next delimiter
        pos = p + 1
        For i = 0 To UBound(delims)
            j = InStr(start, str, delims(i))
            If j > 0 And j < pos Then
                pos = j
            End If
        Next i
        ' Extract the substring between the start and the delimiter
        If pos > start Then
            words(UBound(words)) = Mid(str, start, pos - start)
            ReDim Preserve words(UBound(words) + 1)
        End If
        ' Move the start position to the next character after the delimiter
        start = pos + 1
    Loop
    ' Add the last substring to the array
    If Len(words(UBound(words))) > 0 Then
        words(UBound(words) - 1) = Mid(str, start - 1, Len(str) - start + 2)
    End If
    ' Print the resulting words
    For i = 0 To UBound(words) - 1
        Debug.Print words(i)
    Next i
End Sub

VBA code to split multiple delimiters using Instr and Mid functions

Code Breakdown:

  • First, the code declares several variables, including a string variable “str” that contains the input string, an array variable “delims” that contains the delimiter characters, a dynamic string array “words” that will hold the resulting substrings, and several loop control variables.
  • The input string is set to “John,Peter;Max-Thomas!Williams-Lewis“.
  • The delimiter characters are set to a one-dimensional array of strings containing “,“, “;“, ““, and “!“.
  • The dynamic string array “words” is initialized with a length of 0.
  • The loop control variable “start” is initialized to 1, and the variable “p” is set to the length of the input string.
  • A Do While Loop is started, which will continue until “start” exceeds “p” (the length of the input string).
  • Within the loop, the code finds the position of the next delimiter character by iterating through the “delims” array and using the “InStr” function to find the first occurrence of each delimiter character in the input string starting at the “start” position. If a delimiter character is found and its position is less than the current position (“pos“), then “pos” is updated to the position of the delimiter.
  • The code then extracts the substring between the “start” position and the delimiter position (if there is one) using the Mid function and adds it to the “words” array using the “ReDim Preserve” statement to increase the length of the array by one.
  • The “start” position is updated to the character immediately following the delimiter.
  • Steps are repeated until all delimiter characters have been found and substrings have been added to the “words” array.
  • After the loop has finished, the code checks if there is a non-empty substring remaining after the last delimiter. If there is, it is added to the “words” array using a similar process as in the above step.
  • Finally, the code prints each substring in the “words” array using a For Loop that iterates through each element in the array (except the last one, which was added as an empty element in step 4). The “Debug.Print” statement outputs the substring to the Immediate window in the VBA editor.

You have to run the code with the F5 key, and you will get your inputted data split in the Immediate window like in the image below.

Final output showing in the immediate window


How to Split Strings in Excel Using Multiple Delimiters Without VBA

You can split strings using multiple delimiters without VBA. To do this, we used the TEXTSPLIT function of Excel. The function splits any text using the delimiters. It works like the Text-to-Columns feature. The same dataset we used here. We will split the data for delimiters (comma and hyphen).

  • Move to cell C5 and insert the below formula.
=TEXTSPLIT(B5,{",","-"})

In the above syntax, the TEXTSPLIT first takes the text of the B5 cell, and then we set the delimiters. We have set multiple delimiters here, like commas and hyphens. The function will split the strings where it finds the delimiters.

  • After that, press ENTER, and drag down the Fill Handle tool for other cells.

Finally, you will get the below output.

Note:

This super helpful function is available only in Excel 365 and Excel for Web.

TEXTSPLIT function split multiple delimiters without VBA


Frequently Asked Questions

  • What should I do if the delimiter is not found in the string?

If the delimiter is not found in the string, the Split function will return a single-element array with the entire string. You should use error handling to ensure that your code does not fail in this scenario.

  • Can I use special characters as delimiters in the Split function?

Yes, you can use special characters as delimiters in the Split function. However, you need to escape these special characters using a backslash () character. For example, if you want to use the asterisk (*) as a delimiter, you should escape it like this: “*“.

  • How do I split a string with multiple delimiters and ignore empty substrings in VBA?

To split a string with multiple delimiters and ignore empty substrings in VBA, you can use the “Split” function and then filter out any empty elements in the resulting array. For example, to split a string using commas, semicolons, and dashes as delimiters and ignore any resulting empty substrings, you could use the following code: “words = Filter(Split(str, Array(“,”, “;”, “-“)), vbNullString, False)“.


Things to Remember

  • If the delimiter is not found in the string, the Split function will return a single-element array with the entire string. You should use error handling to ensure that your code does not fail in this scenario.
  • The VBA Split function splits the string based on the first delimiter it encounters. Therefore, the order of the delimiters in the array matters. For example, if you have the delimiters “!” and ““, and you want to split the string.
  • If the string contains consecutive delimiters or the delimiter is at the beginning or end of the string, the Split function may create empty elements in the resulting array. You should handle these empty elements appropriately in your code.

Download Practice Workbook

Download the following practice workbook. It will help you understand the topic more clearly.


Conclusion

We have come to the end of our article. This article will help you to realize the basics of VBA to split with multiple delimiters. We can use the VBA code to make the process more robust as a result it will save us valuable time. If you find any problem or issue regarding using the VBA code you can contact us through emails or comments. We are here to solve any kind of Excel-related problem. Till then goodbye. Keep supporting us.


Related Article

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.
Fahim Shahriyar Dipto
Fahim Shahriyar Dipto

Fahim Shahriyar Dipto is a graduate of Mechanical Engineering at BUET. With over 1.5 years of experience at Exceldemy, he authored 70+ articles on ExcelDemy. He has expertise in designing worksheets at You’ve Got This Math. Currently, He is a Team Leader at Brainor. Dipto's passion extends to exploring various aspects of Excel. Beyond tech, he enjoys creating engaging kids' worksheets using Illustrator. A dedicated employee and innovative content developer, He incorporates a commitment to academic excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo