The most efficient way to split a string into a separator, ignoring some instances of the specified separator using excel vba

Question

I have a very redundant piece of old code that I created when I first discovered and decided to learn vba (and I have a great way). I used this code to cycle through cells containing multiple values ​​separated by commas. However, there are times when I cannot just use something like the Split(string,",") function Split(string,",") , because some of the values ​​have commas inside this value (example value: [blah blah, so blah blah]). In cases where these brackets exist (they are around each value in which there is a comma), I developed a rather long approach, which is my old method, to correctly split the values, dump them into an array, and then continue with another job . But now I decided to review the code and set the accuracy. Here are some examples.

Examples of data that can be found in one cell:
^{Please note: this is the data that suppliers send to us, we do not have control over what they enter or how they enter it. This is a simple example showing the essence of how data is usually provided in some cases.}

 Available on 2 sides: Silkscreen,[full: color, covers entire face],Pad Print: One color,[heat transfer, may bleed] 

Values:

• Available from two sides: Silkscreen
• [full: color, full face coverage]
• Folder Printing: single color
• [heat transfer, may bleed]

What I'm looking for:
I am looking for a more efficient and simple method to correctly separate values ​​(keeping brackets for values ​​that have them).

I believe that I was able to create a much more efficient and compact method for handling instances that do not include brackets using the following code

New code (under development): I have problems with the fact that I do not know how to efficiently and accurately split cells using parentheses.

 Sub Test() Dim rngXid As Range, RegularColons As New Collection, UpchargeColons As New Collection, additionals As Range, upcharges As Range, Colon, UpchargeColon Dim Values() As String, endRange As Long, xidMap As Object, xid As String, NumberofValues As Integer endRange = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row Set xidMap = getXidMap(ActiveSheet.Range("A2:A" & UsedRange.Rows.Count)) 'Map products for quicker navigation Set additionals = ActiveSheet.Range("AJ:AK"): Set upcharges = ActiveSheet.Range("CS:CT") Set RegularColons = FindAllMatches(additionals, ":") 'This returns all instances/cells that contain a colon in the specified columns If Not RegularColons Is Nothing Then For Each Colon In RegularColons xid = ActiveSheet.Range("A" & Colon.Row).Value If InStr(1, Colon.Value, "[") = 0 Then 'If no brackets then simply split Values = Split(Trim(Colon.Value), ",") Else 'This is where I'm at a lose for a more effective method '-----------Populate Values array with Colon.Value while watching out for brackets-------- End If Set rngXid = xidMap(xid).EntireRow.Columns(upcharges) 'set to this specific product For ColorLocation = LBound(Values) To UBound(Values) 'cycle through each value in Values array If Not InStr(1, Values(ColorLocation), ":") = 0 Then 'Only proceed if the value has a colon Set UpchargeColons = FindAllMatches(rngXid, Values(ColorLocation)) 'Searching other columns for this value If Not UpchargeColons Is Nothing Then For Each UpchargeColon In UpchargeColons 'If found in other columns proceed to replace colon UpchargeColon.Value = Replace(UpchargeColon.Value, ":", " ") Log UpchargeColon.Range, "Removed Colon from Additional Color/Location Upcharge", "Corrected" 'This is a custom sub of mine to record the change Next UpchargeColon End If Values(ColorLocation) = Replace(Values(ColorLocation), ":", " ") End If Next ColorLocation Log Colon.Range, "Removed Colon(s) from Additional Color/Location Value(s)", "Corrected" Next Colon End If End Sub 

I was looking at possible ways to do this, and the one that keeps hanging around is Regex , which admittedly I have absolutely no experience, although I already heard about it. So, I tried to read some sites on it, such as, of course, the msdn documentation . My observations / thoughts, trying to learn a little more about this method:

• It is absolutely terribly complex and overwhelming. So much so that I should refrain from crawling into a corner, taking the position of the fetus, and shout out my eyes.
• I can’t find anything that could suggest how I could enable this function with my need to split the string accordingly, at least as far as I can tell. But maybe I'm just so overloaded with all seemingly random character sequences

So my question is:
What would be the most efficient way to accurately separate values ​​in cells containing brackets?

Answer 1

Regular expressions (the so-called "regular expressions") are really scary, but they are also a powerful tool, and VBA supports them if you add a link to the Microsoft VBScript Regular Expressions 5.5 library.

With it, you can create a RegExp object that will give you a MatchCollection , which is, well, a collection of Match objects.

Here's how to use them:

 Sub Test() Const value As String = _ "Available on 2 sides: Silkscreen,[full: color, covers entire face],Pad Print: One color,[heat transfer, may bleed]" Const pattern As String = _ "(\[[^\]]+\]|[^,]+)" Dim regex As New RegExp regex.Global = True regex.pattern = pattern Dim matches As MatchCollection Set matches = regex.Execute(value) Dim m As Match For Each m In matches Debug.Print Trim(m.value) 'value will preserve any leading/trailing spaces Next End Sub 

Note that pattern pretty much matches Laurel's answer :

 (\[[^\]]+\]|[^,]+) 

Without specifying that you want to combine a comma, you disagree with it (regardless of whether it is there or not) - therefore, the above code outputs this:

 Available on 2 sides: Silkscreen [full: color, covers entire face] Pad Print: One color [heat transfer, may bleed] 

You can easily MatchCollection over MatchCollection to fill the array if you need it.

Answer 2

There are other ways, but this regex looks pretty fast:

(\[[^\]]+\]|[^,]+),?

Explanation:

\[ and \] are shielded versions of [ and ]

Essentially, it searches for \[ , gets all non-brackets [^\]] , and then \] . Otherwise | it will receive all non-commas [^,] . Ambient () makes the capture group. ,? means the comma may or may not be.

Answer 3

One way is to take the commas in square brackets and replace them with Chr (184). These little guys are very much like commas.

After replacing the brackets with commas, you can use the usual Split () Here is the code to replace:

 Sub parser() Dim s As String, s1 As String, s2 As String, pseudo As String Dim switch As Boolean, temp As String, CH As String pseudo = Chr(184) s1 = "[" s2 = "]" s = [A1] switch = False temp = "" For i = 1 To Len(s) CH = Mid(s, i, 1) If CH = s1 Or CH = s2 Then switch = Not switch If switch Then CH = Replace(CH, ",", pseudo) temp = temp & CH Next i Range("A2").Value = temp MsgBox s & vbCrLf & temp End Sub 

Answer 4

 Function Splitter(s) Dim p As Long, b As Long, l As String Dim c As Long, s2 As String, arr, n If InStr(s, "[") = 0 Then arr = Split(s, ",") Else c = 0 For p = 1 To Len(s) l = Mid(s, p, 1) If l = "," And c = 0 Then Mid(s, p, 1) = vbNull Else If l = "[" Then c = c + 1 If l = "]" Then c = c - 1 End If Next p arr = Split(s, vbNull) End If Splitter = arr End Function