Solved One more RegEx help please

[silentwolf]

Sorry for posting so many threats today but need to finish a project of my self today and am a bit stuck at times.

I got this RegEx code from someone here on the forum but not sure who it is right now! Sorry

Public Function ReturnMandatsnummer(strText As String) As String
    Dim RegEx As RegExp
    Set RegEx = New RegExp

    RegEx.Global = True
    RegEx.Pattern = "\Mandatsnummer: (\S+)"
    Dim mc As MatchCollection
    Set mc = RegEx.Execute(strText)

    If mc.Count <> 0 Then
        Dim Item As Variant
        For Each Item In mc
            ReturnMandatsnummer = Item
        Next Item
    End If

End Function

This function finds all the Mandatnummer in a string of text
Works perfect!

However.

As I need to split different parts of the string providet into other fields I like to do following.

String is something like this

Some text in front whatever Mandatsnummer: V-015043474-20180522 Some Text I would need after this Number REF: 0121452145222

This Text can be several words and even Numbers included however I like the whole Some Text I would need after this Number to be returned in a function.

The above Code Finds all Mandatnummer correctly not sure if I need this to find the Text between Mandatsnummer: xxxxxx and REF:

In the string could be either Mandatsnummer: xxxx and REF: straight afterwards = NO ACTION
or Mandatsnummer:xxxx and Auftraggeberreferenz: = NO ACTION
or Mandatsnummer: xxxx and Zahlungsreferenz: = NO ACTION


Many thanks for help again

 

[cheekybuddha]

If all strings contain 'Mandatsnummer:' and 'Ref:' then:
1. Use InStr() to locate start position of 'Mandatsnummer:'
2. Add length of 'Mandatsnummer:' to get to start point of actual Mandatsnummer
3. Use InStr() to locate start position of 'Ref:'
4. Use Mid() with start value from step 2, and length using step 3 - step 2 to get string between 'Mandatsnummer:' and 'Ref:'
5. Use your function above to get the actual Mandatsnummer.
6. Use Replace() on the string from step 4 and replace the Mandatsnummer with the result of step 5 with an empty string (vbNullString or "")
7. Use Trim() on the result to clear leading (and trailing) spaces
8. The result should be everything in between the Mandatsnummer and 'Ref:'

 

[silentwolf]

Ok I will try that ) Thanks

 

[cheekybuddha]

In the string could be either Mandatsnummer: xxxx and REF: straight afterwards = NO ACTION
or Mandatsnummer:xxxx and Auftraggeberreferenz: = NO ACTION
or Mandatsnummer: xxxx and Zahlungsreferenz: = NO ACTION

Are you saying that you also would want to find text between 'Mandatsnummer: xxx' and 'Auftraggeberreferenz:' and between'Mandatsnummer: xxx' and 'Zahlungsreferenz:' too?
ie. the second boundary could be any of 'Ref:', 'Auftraggeberreferenz:' or 'Zahlungsreferenz:' ?

 

[silentwolf]

No it is only for text between End of Mandatsnummer: xxx-xxx*-xxx and REF:
If there is a text I need it but if there is Auftraggeber or Zahlung then not this is already processed

 

[cheekybuddha]

Let us know how you get on.

 

[silentwolf]

Yes I will but I am taking a short break from it getting headache

Cheers

 

[silentwolf]

So this is what I got at present..

Public Function TestFunction(strText As String) As String
    If strText Like "*Mandatsnummer*" Then
        If strText Like "*Zahlungsreferenz*" Or strText Like "*Auftraggeberreferenz*" Then
            TestFunction = "NA"
        Else
            mStartPos = InStr(strText, "Mandatsnummer")
            mEndPos = InStr(strText, "REF:") - 1
            mIntLength = mEndPos - mStartPos
            TestFunction = Mid(strText, mStartPos, mIntLength)
        End If
    End If
End Function

It does not through any Errors and it is running through a Test Query alright
However I still got issues to find the length of the Mandatsnummer:
It can as with everything be in different kind of formating
such as

Mandatsnummer: AT0323-2020202-0002
Mandatsnummer: AT000474483001111
Mandatsnummer: 1841090371
Mandatsnummer: 2917 22025-212647 v. 09.11.2021 KFZ Werkstatt

for the last example the is Mandatsnummer: 2917

So 22025-212647 v. 09.11.2021 KFZ Werkstatt would need to be filtert out.

I did try to get with the RegEx function but without luck is there a way to find the length of the String returning from the RegEx function?


Cheers (

 

[cheekybuddha]

What about steps 5,6,7 ?

Also, why are your start, end and length variables declared at module level?

 

[silentwolf]

Well I am not sure how to use or get the length of the mandatsnummer out of my function

Also, why are your start, end and length variables declared at module level?

because there are many of this kind of functions in that module so I dont need to rewrite them over and over again

 

[silentwolf]

I think I am getting there just had a aha effect lol maybe not the elegant way but ...

 

[cheekybuddha]

Not tested, but I imagine something like this will get you pretty close:

Public Function TestFunction(strText As String) As String

  Dim ret             As String
  Dim nummer          As String
  Const MANDATSNUMMER As String = "Mandatsnummer:"      ' Note colon is included
  CONST REF           As String = "REF:"

  ret = "NA"
  If strText Like "*Zahlungsreferenz*" _
  Or strText Like "*Auftraggeberreferenz*" Then
    GoTo ReturnResult
  End If

  If strText Like "*" & MANDATSNUMMER & "*" Then
    mStartPos = InStr(strText, MANDATSNUMMER) + Len(MANDATSNUMMER)
    mEndPos = InStr(strText, REF)
    mIntLength = mEndPos - mStartPos
    ret = Mid(strText, mStartPos, mIntLength)
    nummer = ReturnMandatsnummer(strText)
    ret = Replace(ret, nummer, vbNullString)
    ret = Trim(ret)
  End If

ReturnResult:
  TestFunction = ret

End Function

 

[silentwolf]

Hi David,

oh I did manage with this function below

Public Function TestFunction03(strText As String) As String
    If strText Like "*Mandatsnummer*" Then
        If strText Like "*Zahlungsreferenz*" Or strText Like "*Auftraggeberreferenz*" Then
            TestFunction03 = ""
        Else
            mStartPos = InStr(strText, "Mandatsnummer")
            mEndPos = InStr(strText, "REF:") - 1
            
            Dim strMandatNummer As String
            strMandatNummer = ReturnMandatsnummer(strText)
                        
            Dim intMandatLength As Integer
            intMandatLength = Len(strMandatNummer)
            
            'New Starting Position
            Dim newStartPos As Integer
            
            newStartPos = mStartPos + intMandatLength
            
            mIntLength = mEndPos - newStartPos
            
            TestFunction03 = Mid(strText, newStartPos, mIntLength)

        End If
    End If
End Function

Not tested, but I imagine something like this will get you pretty close:

Public Function TestFunction(strText As String) As String

  Dim ret             As String
  Dim nummer          As String
  Const MANDATSNUMMER As String = "Mandatsnummer:"      ' Note colon is included
  CONST REF           As String = "REF:"

  ret = "NA"
  If strText Like "*Zahlungsreferenz*" _
  Or strText Like "*Auftraggeberreferenz*" Then
    GoTo ReturnResult
  End If

  If strText Like "*" & MANDATSNUMMER & "*" Then
    mStartPos = InStr(strText, MANDATSNUMMER) + Len(MANDATSNUMMER)
    mEndPos = InStr(strText, REF)
    mIntLength = mEndPos - mStartPos
    ret = Mid(strText, mStartPos, mIntLength)
    nummer = ReturnMandatsnummer(strText)
    ret = Replace(ret, nummer, vbNullString)
    ret = Trim(ret)
  End If

ReturnResult:
  TestFunction = ret

End Function

Thanks for the Code!

Sorry that you gone through providing the Code that is why I send a new message for beein on it.. (

Your Code still provides the Actuall Mandat Number so without "Mandatsnummer."

Besides that all good!!

But as mine seams to work fine I will stick to that one even it is not that of a pretty code I guess )

Cheers all for your help!!!

Albert

 

[cheekybuddha]

oh I did manage with this function below

Excellent!

Your Code still provides the Actuall Mandat Number so without "Mandatsnummer."

Interesting! Does your function ReturnMandatsnummer() return the label "Mandatsnummer:" at the beginning as well as the actual Mandatsnummer?

 

[silentwolf]

Hi David,

my function returns everything after the Mandatsnummer: xyz to the REF: so everything in between.

It is a bankstatement as you for sure guessed but there are as always many different ways they provide the Data.

And in some I just need what ever is after the Mandatsnummber:xxx and before REF:
It is some Information on what the Bill was about.

Paylife Payment from 11/11/2024 for example

In most cases they have those Information in Auftraggeberreferenz: or in Zahlungsreferenz but some do it different again of course and not providing that information after those two Categories and enter this info without those Tags if you will.

 

[cheekybuddha]

my function returns everything after the Mandatsnummer: xyz to the REF: so everything in between

I see.

Then I'm not sure why the Mandat Number was returned in my function since it should have been replaced with an empty string.

But, no problem! You have created something that works - that is the important thing!