Creating an array of different strings for Instr to look for

[sika14307]

Hi,

I am working on a project to strip invoice numbers from email data for my job. Using different VBA Scripts, I have managed to create a function that find the next 8 digit number after the substring "invoice". However, I want to expand the function to cover a variation of words ("inv", "bill number") but I am stuck on how to. I know that I can't have multiple strings to look for with the function INSTR and I think I need an array. I am stuck on how to rework this vba.

Public Function GetInvNum(cell As Range)

Dim s As String
Dim i As Integer
Dim answer
Dim counter As Integer
Dim CharPos As Long

'get cell value
s = cell.Value

'set the counter
counter = 0

CharPos = InStr(1, s, "invoice", vbTextCompare) + 2

    If CharPos > 2 Then
        'loop through the entire string
        For i = CharPos To Len(s)
            'check to see if the character is a numeric one
            If IsNumeric(Mid(s, i, 1)) = True Then
                'add it to the answer
                answer = answer + Mid(s, i, 1)
                counter = counter + 1
                'check to see if we have reached 8 digits
                If counter = 8 Then
                    GetInvNum = answer
                    Exit Function
                End If
             Else
             'was not numeric so reset counter and answer
             counter = 0
             answer = ""
            End If
    Next i
End If
End Function

 

[Peter_SSs]

The changes I made to .pattern was Invoice | credit memo | credit note | billing number

That isn't an actual line of code so I cannot test it.

However, along similar lines to Eric to limit how far ahead the function looks for the 8-digit number, this would be my adaptation.

Function InvNum(s As String, rng As Long) As String
  With CreateObject("VBScript.RegExp")
    .IgnoreCase = True
    .Pattern = "(Invoice|credit memo|credit note|Billing number)([\D]{0," & rng & "})(\d{8})(?=\D|$)"
    If .Test(s) Then InvNum = .Execute(s)(0).SubMatches(2)
  End With
End Function

.. and with your previous sample data (no result for the first one since no 8-digit number is within 20 characters of any of the search terms.)

sika14307.xlsm
	A	B
16	The attached invoice has been rejected due to the reasons given below.PO Number (if any)11111122Invoice Number1234567Sunpower Id Code101115198Primary Reason for RejectionOther Details From Sender: Please be advised that we cannot process this invoice, Bill to information is not aligned with PO.
17	Dear Supplier: This is to hereby acknowledge that Company 1, Inc. Accounts Payable has rejected the following invoice which Company received for payment on 01/8/2022. Invoice Number: 12345678 Invoice Date: 2/29/2022 Due to the following reason: Invoice was sent in more than once. Invoices should not be sent in again regarding status. Please contact customer care for status of unpaid invoices. Please do not reply to this message.	12345678
Sheet1

Cell Formulas
Range	Formula
B16:B17	B16	=InvNum(A16,20)