Reg Ex Help please

[silentwolf]

Hi guys,

I know some of you guys are pretty awsome in Reg Ex but I am not lol--- So I was hoping for some help with following String

Kunner GmbH Oberwasser 69 AT 6666 Walddorf IBAN: AT75 1683 0021 0000 0166 DOC/2319/2667.60/20220729 REF: 195302208230116494800000001
Josef und Anne Schöller IBAN: DE11 1234 1052 0022 2345 02 mtl. Einigung u. Kontrolle REF: 00101220117141103Y800009ZQ01

I would like to get in those above strings following result marked as bold
IBAN can be from Germany or from Austria so the length of the IBAN is different!


Basically the text between end of IBAN and before REF:

Could someone help me with this please.

Many thanks

Albert

 

[plog]

This is more about logic than regex. You need to come up with hard and fast rules that you can logically implement. You've only given 2 examples, so I can start you off, but you need to verify what I say is the rule is actually the rule for all the data you have:

1. Go to the position after the first occurence of 'IBAN' in the string.
2. From there go to the position of the first number.
3. From there, find the position of the first letter.
4. Extract the substring from position 3 above to the start of 'REF'.

Read those steps carefully, is that absolutely correct? Do you have any data that will fail if you do those 4 steps above? Think of ways your data might break that and ensure you don't have any data like that (e.g. 2 instances of 'IBAN' in your string, no instance of 'REF' in your string, letters after 'IBAN' that should not be included in the extracted substring, etc.)

If those 4 steps above are absolutely dead nuts on for every case, then you can start implementing a custom function inside a module to do them.

 

[silentwolf]

Hi,

there are not always the same as mentioned once I use a query to filter it out.
And I am used to use instr or instrRev functions to get the possitions as you mentioned but was wondering if there is in Reg Ex a simpler way of doing it.

I will try anyway with those functioned mentioned )

Cheers

 

[Josef P.]

Regular expression: IBAN:\s*([A-Z]{2}[0-9 ]+)\s+(.+?)\s+REF:

ChatGPT is very useful for regex

 

[silentwolf]

Lol really hihi I will try many thanks )

Not sure how you use chatGPT need to look into it I guess )

 

[Josef P.]

Prompt for ChatGPT:

I need a regular expression for:
1)
Kunner GmbH Oberwasser 69 AT 6666 Walddorf IBAN: AT75 1683 0021 0000 0166 DOC/2319/2667.60/20220729 REF: 195302208230116494800000001

2)
Josef und Anne Schöller IBAN: DE11 1234 1052 0022 2345 02 mtl. Einigung u. Kontrolle REF: 00101220117141103Y800009ZQ01

match:
1) : DOC/2319/2667.60/20220729
2): mtl. Einigung u. Kontrolle

Text between IBAN (followed by 2 chars and undefined count of digits or spaces) and "REF:"

ChatGPT - Regex für Text Extraktion

Shared via ChatGPT

chatgpt.com

 

[NauticalGent]

I was going to use ChatGPT to write this Sub as well, but was going to let some of the RegEx folks weigh in. Since Josef "broke the seal", try this:

Sub ExtractIBANSection()
    Dim regex As Object
    Dim matches As Object
    Dim inputText As String
    Dim pattern As String
    Dim result As String
    
    ' Create regex object
    Set regex = CreateObject("VBScript.RegExp")
    
    ' Define pattern to capture everything between IBAN and REF
    pattern = "(IBAN:\s*[A-Z]{2}\d{2}[\d\s]+.*?)(?=\s*REF:)"
    
    ' Example input (replace with your actual input)
    inputText = "Kunner GmbH Oberwasser 69 AT 6666 Walddorf IBAN: AT75 1683 0021 0000 0166 DOC/2319/2667.60/20220729 REF: 195302208230116494800000001"
    
    ' Configure regex
    With regex
        .Pattern = pattern
        .Global = False
        .IgnoreCase = True
        .MultiLine = False
    End With
    
    ' Execute regex
    If regex.Test(inputText) Then
        Set matches = regex.Execute(inputText)
        result = Trim(matches(0).SubMatches(0)) ' Extract match and trim spaces
        MsgBox "Extracted Section: " & result, vbInformation, "Regex Result"
    Else
        MsgBox "No match found.", vbExclamation, "Regex Result"
    End If
    
    ' Clean up
    Set regex = Nothing
    Set matches = Nothing
End Sub

Be sure to add the reference for RegEx:
Open the VBA Editor (ALT + F11).
Go to Tools > References.
Find and check Microsoft VBScript Regular Expressions 5.5.
Click OK.