Here’s an overview of splitting cells with multiple delimiters using Excel VBA.
How to Launch the VBA Editor in Excel
- Go to the Developer tab and choose Visual Basic.
Note: By default, the Developer tab remains hidden. In that case, you have to enable the Developer tab.
- Go to the Insert tab and choose Module.
- You’ll get a new window to insert the code.
- Run the code with F5 or save the file as an .xlsm before closing the window.
VBA to Split with Multiple Delimiters in Excel: 2 Easy Ways
We have taken a dataset of “Student Information of ABC College”. The Name, ID, and Department Names are separated with delimiters. We put a comma (,) and a hyphen (-) as delimiters. We’ll separate the values in respective columns.
Method 1 – Combining VBA Replace and Split Functions with For Loop
Case 1.1 – Split Array of Strings Based on Array of Multiple Delimiters
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 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.
- 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.
- 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.
- 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.
Case 1.2 – Split Strings in a Cell Range Based on Multiple Delimiters
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“.
- 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“.
- The code uses the Split function to split the data carrying hyphens.
- 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
Method 2 – Using the VBA InStr Function with a Do While Loop
- Use this code:
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:
- 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 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.
- 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.
- 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
- Move to cell C5 and insert the following formula.
=TEXTSPLIT(B5,{",","-"})
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.
- Press Enter, and drag down the Fill Handle tool for other cells.
Note:
This 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 the Practice Workbook
Related Article
- Excel VBA: Split String into Rows
- Excel VBA: Split String by Character
- How to Split a String into an Array in VBA
