Optional parts of regex pattern in vba

Question

I am trying to build regex pattern for the text like that

numb contra: 1.29151306 number mafo: 66662308
numb contra 1.30789668number mafo 60.046483
numb contra/ 1.29154056 number mafo: 666692638
numb contra     137459625
mafo: 666692638
mafo: 666692638 numb contra/ 1.29154056

Here's the pattern I could build

contra?.\s+?(\d+\.?\d+)(.+mafo.?\s+(\d+\.?\d+))?

It works fine for all the lines except the last one. How can I implement all the possibilities to include the last line too?

Please have a look at this link https://regex101.com/r/pSThAU/1 All is OK as for contra but not as for mafo

Answer 1

I think the key here is to make your regexp do less and your vba do more. What I think I see here is either the word 'mafo' or 'contra' and a number following. Don't know what order or whether each is present or how many times. So you can scan each of your strings for ALL occurrences with a regexp like this:

(?:^|[^A-Z])(?:(mafo)|(contra))[^A-Z]\s*(\d*\.?\d+)

Then process it with some VBA code like this that I created in Excel:

Sub BreakItUp()
Dim rg As RegExp, scanned As MatchCollection, eachMatch As Match, i As Long, col As Long

Set rg = New RegExp
rg.Pattern = "(?:^|[^A-Z])(?:(mafo)|(contra))[^A-Z]\s*(\d*\.?\d+)"
rg.IgnoreCase = True
rg.Global = True
i = 1
Do While (Not IsEmpty(ActiveSheet.Cells(i, 1).Value))
    Set scanned = rg.Execute(ActiveSheet.Cells(i, 1).Value)
    col = 2
    For Each eachMatch In scanned
        ActiveSheet.Cells(i, col).Value = eachMatch.SubMatches(0) & eachMatch.SubMatches(1)
        ActiveSheet.Cells(i, col + 1).Value2 = "'" & eachMatch.SubMatches(2)
        col = col + 2
    Next eachMatch
    i = i + 1
Loop
End Sub

That MatchCollection object will get one item for each Match that occurs and the subMatches array contains each capturing group. You should be able write your own logic within this processing loop to interpret what was extracted. When I ran it on your data it created all the fields in blue:

Notice I added a line to your data that had two contra entries and one mafo and it found all the occurrences. You should be able to modify this to interpret the meanings.