Extract a variable number of parts of text from a string

[Gasman]

Here are my figures on 3 runs. Not much in it TBH, and if anything, loop is quicker?
DB is on an SSD and the laptop is an i7-4710MQ CPU @ 2.50GHz 2.50 GHz with 16GB RAM

 

[NauticalGent]

It doesn't surprise me as RegEx is a powerhouse - but for this issue, it is like using a bazooka to kill a mosquito.

 

[NauticalGent]

Adapted with RegEx:

Function ExtractBetweenDelimitersRegex(ByVal InputString As String, ByVal StartDelimiter As String, ByVal EndDelimiter As String) As Collection
    Dim Regex As Object
    Dim Matches As Object
    Dim Match As Object
    Dim Results As New Collection
    Dim Pattern As String
    
    ' Build the regex pattern dynamically
    Pattern = StartDelimiter & "(.*?)" & EndDelimiter
    
    ' Create the regex object
    Set Regex = CreateObject("VBScript.RegExp")
    With Regex
        .Pattern = Pattern
        .Global = True ' Ensure it matches all occurrences
        .IgnoreCase = True ' Optional: make case-insensitive
    End With
    
    ' Execute the regex
    Set Matches = Regex.Execute(InputString)
    
    ' Loop through matches and extract the captured groups
    For Each Match In Matches
        Results.Add Match.SubMatches(0) ' Add the captured group to the collection
    Next Match
    
    ' Return the collection
    Set ExtractBetweenDelimitersRegex = Results
End Function

Call it with:

Sub TestExtractRegex()
    Dim Results As Collection
    Dim Item As Variant
    Dim TestString As String

    TestString = "Easily extract #text# between two #strings# with this #VBA Function#. This user-defined function #(UDF)# can extract"
    
    Set Results = ExtractBetweenDelimitersRegex(TestString, "#", "#")
    
    For Each Item In Results
        Debug.Print Item ' Outputs each extracted value to the Immediate Window
    Next Item
End Sub

Immediate window:
text
strings
VBA Function
(UDF)

 

[Gasman]

I will try the O/P's string tomorrow. I just ran @theDBguy 's code. I did not review it, so no idea what it was processing.

 

[NauticalGent]

I didn't run my timer...but...to my naked eye, the regex seemed faster

 

[cheekybuddha]

All Arnel's code needs is to populate a collection so that it returns all of the items

It already populates a collection (Matches)

No need to iterate that one and populate another one. Just return it!

 

[AHeyne]

It already populates a collection (Matches)

No need to iterate that one and populate another one. Just return it!

This is not quite correct, the type of 'matches' is 'IMatchCollection2'.
It can be iterated with VBA like a Collection, but if an object of type 'Collection' is required for further/superordinate processing, it must be created explicitly.