arrays – Most efficient way to split a string up by a delimiter while ignoring certain instances of said delimiter using excel vba

arrays – Most efficient way to split a string up by a delimiter while ignoring certain instances of said delimiter using excel vba

There are other ways, but this regex seems to be pretty fast:

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

Explanation:

[ and ] are escaped versions of [ and]

Essentially, its 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

enter

arrays – Most efficient way to split a string up by a delimiter while ignoring certain instances of said delimiter using excel vba

Regular Expressions (aka regex) are indeed scary-looking, but theyre also a powerful tool, and VBA supports them, if you add a reference to the Microsoft VBScript Regular Expressions 5.5 library.

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

Heres 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

Notice the pattern is pretty much that in Laurels answer:

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

By not specifying that you want to match a comma, youre not matching it (whether its there or not) – hence, 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 iterate a MatchCollection to populate an array if you need one.

Leave a Reply

Your email address will not be published. Required fields are marked *