Search For 8 digit number after a substring in a row

[sika14307]

Hi,

I am trying to create a public function that extract an eight digit number after a variation of the string, invoice number, in a row.

For example, if a row has "invoice rejection, po 90449033, invoice number 12345678", I want the function to extract only the invoice number.

The closest I was able to find is a function that counts the nearest 8 digit number in a row of text but not sure how to expand it to only extract the number after a certain phrase.

By variation of invoice number, I mean to extract the number after determining if it meets multiple options (inv or invoice number or invoice)

 

[Michael M]

BAsed on your sample, this will work

=RIGHT(A1,8)

More sample data may give a clearer picture of what you actually need.

 

[Z51]

You're going to have to specify the exact rules to determine what should be extracted. It can't just be "the 8-digit number after inv" or "invoice", as that might include the PO# in your referenced example.

 

[sika14307]

BAsed on your sample, this will work

=RIGHT(A1,8)

More sample data may give a clearer picture of what you actually need.

This is email data so the data isn't very clean. I am trying to get only invoice number.

 

[sika14307]

You're going to have to specify the exact rules to determine what should be extracted. It can't just be "the 8-digit number after inv" or "invoice", as that might include the PO# in your referenced example.

Hey, I am using email data so I don't except to capture all the invoice number correctly. I am just trying to capture as much as possible, that why I want it to look for different variations of the word invoice.

 

[Michael M]

This will work if the text "invoice number" is exactly the same in each instance, but I'm guessing it won't be.

=LOOKUP(10^10,MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890,FIND("invoice number",A1))),{1,2,3,4,5,6,7,8,9})+0)

 

[sika14307]

This will work if the text "invoice number" is exactly the same in each instance, but I'm guessing it won't be.

=LOOKUP(10^10,MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890,FIND("invoice number",A1))),{1,2,3,4,5,6,7,8,9})+0)

It is close enough thanks

 

[Michael M]

Hope it helps

 

[Peter_SSs]

It is close enough thanks

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

 

[bebo021999]

Alternative:

=IFERROR(MID(A2,SEARCH("invoice number",A2)+15,8)+0,"")