I have a very redundant piece of old code, which I created when I first discovered and decided to learn vba (and man have I come a long way). I was using this code to cycle through cells that contained multiple values delimited with commas. However, there are cases where I can't simply use something such as the Split(string,",")
function because some of the values have commas within that value (example value:[blah blah, so blah blah]). In instances where these brackets exist (they are around every value that has a comma within it) I devised the rather long-winded approach that is my old method to properly split the values up, dump them in an array and then proceed with my other tasks. But, now I've decided to revisit the code and fix the accuracy. Here is some background.
Sample Data that can be found in one cell:
Please Note: This is data that suppliers send us, we don't have control over what they enter or how they enter it. This is a simple example to show the gist of how the data typically is provided in some cases
Available on 2 sides: Silkscreen,[full: color, covers entire face],Pad Print: One color,[heat transfer, may bleed]
Values are:
What I'm looking for:
I'm looking for a more efficient and simpler method to be able to split up values correctly (while keeping the brackets for the values that have them).
I believe I have managed to create a much more efficient and compact method to handle instances that don't include brackets using the following code
New Code (Under Construction): I'm having issues with not knowing how to efficiently and accurately split the cells with brackets
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've been browsing possible ways to do this and the one that keeps sticking out is Regex
, which admittedly I have absolutely no experience with although I have previously heard of it. So, I tried reading up on it a bit using sites like this and of course the msdn documentation. My observations/thoughts while trying to learn a little more about this method are:
So, my question is:
What would be the most efficient way to accurately split up the values in cells that contain brackets?
There are other ways, but this regex seems to be pretty fast:
(\[[^\]]+\]|[^,]+),?
Explanation:
\[
and \]
are escaped versions of [
and]
Essentially, it's looking for a \[
, gets all non brackets [^\]]
, then the \]
. Otherwise |
, it will get all the non commas [^,]
. The surrounding ()
makes it a capture group. ,?
means there may or may not be a comma.
One way is to take the bracketed commas and replace them with Chr(184)'s. These little guys look a lot like commas.
Once the bracketed commas have been replaced, you can use normal Split() Here is some code to do the replacement:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With