Split string on single forward slashes with RegExp

Question

edit: wow, thanks for so many suggestions, but I wanted to have a regexp solution specifically for future, more complex use.

I need support with splitting text string in VBA Excel. I looked around but solutions are either for other languages or I can't make it work in VBA.

I want to split words by single slashes only:

text1/text2- split
text1//text2- no split
text1/text2//text3 - split after text1

I tried using regexp.split function, but don't think it works in VBA. When it comes to pattern I was thinking something like below:

(?i)(?:(?<!\/)\/(?!\/))

but I also get error when executing search in my macro while it works on sites like: https://www.myregextester.com/index.php#sourcetab

Answer 1

You can use a RegExp match approach rather than split one. You need to match any character other than / or double // to grab the values you need.

Here is a "wrapped" (i.e. with alternation) version of the regex:

(?:[^/]|//)+

Here is a demo

And here is a more efficient, but less readable:

[^/]+(?://[^/]*)*

See another demo

Here is a working VBA code:

Sub GetMatches(ByRef str As String, ByRef coll As collection)

Dim rExp As Object, rMatch As Object

Set rExp = CreateObject("vbscript.regexp")
With rExp
    .Global = True
    .pattern = "(?:[^/]|//)+"
End With

Set rMatch = rExp.Execute(str)
If rMatch.Count > 0 Then
    For Each r_item In rMatch
        coll.Add r_item.Value
        Debug.Print r_item.Value
    Next r_item
End If
Debug.Print ""
End Sub

Call the sub as follows:

Dim matches As New collection
Set matches = New collection
GetMatches str:="text1/text2", coll:=matches

Here are the results for the 3 strings above:

1. text1/text2
 text1
 text2

2. text1/text2//text3
 text1
 text2//text3

3. text1//text2
 text1//text2