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 https://regex101.com/ 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 https://stackoverflow.com/questions/22254746/bible-verse-regex 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 https://stackoverflow.com/questions/22254746/bible-verse-regex ' (\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