CODEWITHSUNDEEP
regexexcelvba
Kamui
Kamui

Reputation: 799

How can I split an array in VBA with a delimiter based on a regex condition?

I have an input like:

apple, orange, (pear, banana, grape), mango

that I want to split as:

apple
orange
(pear, banana, grape)
mango

I don't understand the regex fully, but I think I would use ,(?![^()]*)) which I found here - Java split string on comma(,) except when between parenthesis ()

I am using VBA, so if I have the input stored in an array, I would typically do:

array = Split(string, ",")

But this would yield the result as:

apple
orange
(pear
banana
grape)
mango

which I don't want.

I'm pretty sure I can find a way to replace ( and ) with nothing so they disappear from the output, but I don't know how to feed my regex string logic to my VBA formula.

I thought something like this would work:

array = Split(string, ",(?![^()]*\))")

But it doesn't. I did enable the "Microsoft VBScript Regular Expressions 5.5" reference but it didn't seem to help.

Any advice is appreciated.

Thank you,

Upvotes: 1

Views: 332

Answers (2)

DisplayName
DisplayName

Reputation: 13386

another possibility out of RegEx:

Function GetArray(ByVal str As String) As Variant       
    Dim s As String, v As Variant
    For Each v In Split(Replace(str & " ", ")", "("), "(")
        s = s & IIf(Right(v, 1) <> " ", "(" & v & ")", Replace(v, ",", "|"))
    Next

    GetArray = Split(Replace(WorksheetFunction.Trim(s), "| ", "|"), "|")
End Function

which you can use in your main code like:

    Dim myArray As Variant        
    myArray = GetArray("apple, orange, (pear, banana, grape), mango")
    Range("A1").Resize(ubound(myArray) + 1).Value = Application.Transpose(myArray)

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152505

Alternative to RegEx:

Sub mytry()
    Dim str As String
    str = "apple, orange, (pear, banana, grape), mango "

    Dim perenSplt() As String
    perenSplt = Split(Replace(str, ")", ")("), "(")

    str = ""

    Dim i As Long
    For i = LBound(perenSplt) To UBound(perenSplt)
        If InStr(perenSplt(i), ")") Then
            perenSplt(i) = "(" & Replace(perenSplt(i), ",", "|")
        End If
        str = str & perenSplt(i)
    Next i

    Dim finalSplt() As String
    finalSplt = Split(str, ",")

    For i = LBound(finalSplt) To UBound(finalSplt)
        If InStr(str, "(") > 0 Then
            finalSplt(i) = Trim(Replace(finalSplt(i), "|", ","))
        Else
            finalSplt(i) = Trim(finalSplt(i))
        End If
    Next i

    ActiveSheet.Range("A1").Resize(,UBound(finalSplt) + 1) = finalSplt

End Sub

enter image description here

Upvotes: 1

Related Questions