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]

Would this approach work for you?

Function InvNum(s As String) As String
  With CreateObject("VBScript.RegExp")
    .IgnoreCase = True
    .Pattern = "(Invoice|Bill number|InvNum)(.*?\D+)(\d{8})(?=\D|$)"
    If .Test(s) Then InvNum = .Execute(s)(0).SubMatches(2)
  End With
End Function

sika14307.xlsm
	A	B
1
2	Invoice number 23659874 yesterday	23659874
3	I sent bill number 08756325 to you	08756325
4	Nothing here
5	Invoice number error 876545432
6	Invoice no. more than 8 digits first 6547895632 then 8 digits 85236974	85236974
7	You have InvNum 87654543	87654543
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=InvNum(A2)

I don't know what your data might be like but in general I would say be very careful of using very short strings to search for. For example, you mentioned "inv". Such short strings could easily turn up in other strings, not at all related to an invoice number. (eg "You sent invalid quote 34343434")

 

[Eric W]

Try changing this line:

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

to

    CharPos = 0
    words = Array("invoice", "inv", "bill")
    For Each w In words
        CharPos = WorksheetFunction.Max(CharPos, InStr(1, s, w, vbTextCompare) + 2)
    Next w

Peter's RegExp code is probably more efficient.

 

[sika14307]

Would this approach work for you?

Function InvNum(s As String) As String
  With CreateObject("VBScript.RegExp")
    .IgnoreCase = True
    .Pattern = "(Invoice|Bill number|InvNum)(.*?\D+)(\d{8})(?=\D|$)"
    If .Test(s) Then InvNum = .Execute(s)(0).SubMatches(2)
  End With
End Function

I don't know what your data might be like but in general I would say be very careful of using very short strings to search for. For example, you mentioned "inv". Such short strings could easily turn up in other strings, not at all related to an invoice number. (eg "You sent invalid quote 34343434")

Hi,

Thanks for the help, I get a value error when I use that function.

Here is a snippet of the data I have:

email data.xlsx
	A
1	text
2	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.
3	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.
Sheet1

 

[sika14307]

Try changing this line:

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

to

    CharPos = 0
    words = Array("invoice", "inv", "bill")
    For Each w In words
        CharPos = WorksheetFunction.Max(CharPos, InStr(1, s, w, vbTextCompare) + 2)
    Next w

Peter's RegExp code is probably more efficient.

Thanks for the help! It still not capturing the variation in the data.

 

[Peter_SSs]

Sorry, I forgot to add my sample data. I have now added it to my previous post.
My function differs to your original function in that yours would return
87654543 for row 5 even though it contains a 9-digit number
65478956 for row 6 even though this is part of a longer number and an 8-digit number does exist in the text.
Again though, I don't really know what is possible with your data.

Thanks for the help, I get a value error when I use that function.

Here is a snippet of the data I have:

Did you change the .Pattern line at all? If so, what is your pattern line?

 

[Eric W]

Looking at your sample data, my suggestion didn't work since the word "invoice" showed up in multiple places in the text, and not all of them had a number following them. I updated the function as follows:

Function GetInvNum2(MyData As String, MyRange As Long)
Dim words As Variant, w As Variant, x As Long, i As Long, inv As String

    words = Array("Invoice", "inv", "Bill")
 
    For Each w In words
 
        x = 1
        x = InStr(x, MyData, w, vbTextCompare)
        While x > 0
            For i = 1 To MyRange
                inv = Mid(MyData, x + i, 8)
                If IsNumeric(inv) And Left(inv, 1) Like "[0-9]" Then
                    GetInvNum2 = inv
                    Exit Function
                End If
            Next i
            x = InStr(x + 1, MyData, w, vbTextCompare)
        Wend
    Next w
 
End Function

This will search for every instance of the first word in the "words" array, and look for an 8-digit number within 20 characters after it. If not found, it will successively check the other words in the same manner. To call it:

=getinvnum2(A2,20)

The 20 is how many characters to check. Using your data, it returned nothing for A2, since the number after "Invoice Number" was only 7 digits, and 12345678 for A3.

 

[sika14307]

Sorry, I forgot to add my sample data. I have now added it to my previous post.
My function differs to your original function in that yours would return
87654543 for row 5 even though it contains a 9-digit number
65478956 for row 6 even though this is part of a longer number and an 8-digit number does exist in the text.
Again though, I don't really know what is possible with your data.


Did you change the .Pattern line at all? If so, what is your pattern line?

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

 

[sika14307]

Looking at your sample data, my suggestion didn't work since the word "invoice" showed up in multiple places in the text, and not all of them had a number following them. I updated the function as follows:

Function GetInvNum2(MyData As String, MyRange As Long)
Dim words As Variant, w As Variant, x As Long, i As Long, inv As String

    words = Array("Invoice", "inv", "Bill")
 
    For Each w In words
 
        x = 1
        x = InStr(x, MyData, w, vbTextCompare)
        While x > 0
            For i = 1 To MyRange
                inv = Mid(MyData, x + i, 8)
                If IsNumeric(inv) And Left(inv, 1) Like "[0-9]" Then
                    GetInvNum2 = inv
                    Exit Function
                End If
            Next i
            x = InStr(x + 1, MyData, w, vbTextCompare)
        Wend
    Next w
 
End Function

This will search for every instance of the first word in the "words" array, and look for an 8-digit number within 20 characters after it. If not found, it will successively check the other words in the same manner. To call it:

=getinvnum2(A2,20)

The 20 is how many characters to check. Using your data, it returned nothing for A2, since the number after "Invoice Number" was only 7 digits, and 12345678 for A3.

Thanks this captured the most invoices number!

 

[sika14307]

Hi,

Thanks for the help, I get a value error when I use that function.

Here is a snippet of the data I have:

email data.xlsx
	A
1	text
2	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.
3	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.
Sheet1

I just tried it with your sample data and still got a value error. Maybe there is an issue with my excel?