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.
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.
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.
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.
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
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.
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.
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
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.
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
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.
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.
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
- Excel VBA: Split String into Rows
- Excel VBA: Split String by Character
- Excel VBA: Split String by Number of Characters
- How to Split a String into an Array in VBA
