Regex to Extract Bible References from a Text using VBA

I must admit I don’t really understand the complexities of Regex, the text matching spells which manage to make sense whilst appearing like a line of gibberish. Thank heavens for sites like which help idiots like me experiment until I can get what I need.

In this case, I have text strings which contain a bible reference and the reading all together, when I just want to extract the Bible Reference. The post at produced the spell I needed to create a Function in VBA for Microsoft Access to do what I needed.

First you need to enable the VBA Regex Reference, so at the top of the VBA Code Window in Access, select TOOLS/REFERENCES and then check Microsoft VBScript Regular Expressions 5.5 which will enable this function to be recognised.

This is the function:

Function regexReadings(sReading) As String
 ' need to enable Microsoft VBScipt Regular Expressions 5.5 in Tools/References
 ' Regex to extract Bible Verse
 ' (\d)\s([a-z]+)\s(\d+)(?::(\d+))?(\s-\s(\d+)(?:\s([a-z]+)\s*(\d+))?(?::(\d+))?)?

 Dim rExp As Object, rMatch As Object, r_item As Object, result As String
 Set rExp = CreateObject("vbscript.regexp")
 With rExp
     .Global = True
     .MultiLine = False
     .IgnoreCase = True
     .Pattern = "(\d)\s([a-z]+)\s(\d+)(?::(\d+))?(\s-\s(\d+)(?:\s([a-z]+)\s*(\d+))?(?::(\d+))?)?"
 End With
 Set rMatch = rExp.Execute(sReading)
 If rMatch.Count > 0 Then
     For Each r_item In rMatch
         result = result & r_item.Value & " "
     Next r_item
 End If
 regexReadings = result
 End Function

The key thing is the Regex spell:


I have only the vaguest understanding of how it works, but believe me, it does.

Therefore If I invoke

Debug.Print regexReadings("Luke 15:1Now the tax collectors and sinners were all gathering around to hear Jesus.")

It returns “Luke 15:1”

Use as you wish, credit to the author of the Regex Spell, Niet