Help with formula to extract text from field

[jeff_cp]

I need some help with a formula. I've got invoices and invoice line items embedded in a field that I need to extract. Below are some examples and what I'm trying extract. As you can see, the invoice and line item# could fall anywhere in the field, could or could not be wrapped in parentheses and sometimes is duplicated in the field for some reason. Also, the data is being pulled from an ODBC connection so a text to columns option won't work for this.

1) I need to extract 140076-1 from below
(INV 140076-1) CAMRY 05 AT, 4 cylinder,Recycled Original Equipment (ROE) - AT, 4 c

2) I need to extract 134421-2 from below
TRANSMISSION ASSY. CORE REFUND INV 134421-2 (INV 134421-2) TRANSMISSION ASSY.

3) I need to extract 139948-2 from below
INV 139948-2 CORE-DUE ALTERNATOR

4) I need to extract 139136-1 from below
(INV 139136-1) (INV 139136-1) (INV 139136-1) (INV 139136-1) (INV 139136-1) (INV 139136-1) (INV 139

I've been using the formula below which gets some of them but not all of them:
=MID(O4,SEARCH("INV",O4)+4,SEARCH(" ",O4,SEARCH(" ",O4)+2)-SEARCH(" ",O4)-2)

Any help would be greatly appreciated. Thanks!

 

[BobUmlas]

=MID(O4,FIND("INV ",O4)+4,FIND(" ",MID(O4,FIND("INV ",O4)+4,255))-1)

 

[BobUmlas]

And if you have Office 365, you can use:
=LET(f,FIND("INV ",O4)+4,
MID(O4,f,FIND(" ",MID(O4,f,255))-1))

 

[jeff_cp]

Hey Bob. I know this is an older post but I ran into something that I need some help with. The invoice numbers I'm trying to extract are the original invoice numbers for credits or returns. Since the invoice numbers on this system can be up to 7 digits, I had to modify the formula to look at the length of the credit or return invoice to determine the number of characters to extract from the description to pull out the original invoice number.

This is my final formula:

=IFERROR(IF(BP12=1,MID(CG12,FIND("INV ",CG12)+4,3),IF(BP12=2,MID(CG12,FIND("INV ",CG12)+4,4),IF(BP12=3,MID(CG12,FIND("INV ",CG12)+4,5),IF(BP12=4,MID(CG12,FIND("INV ",CG12)+4,6),IF(BP12=5,MID(CG12,FIND("INV ",CG12)+4,7),IF(BP12=6,MID(CG12,FIND("INV ",CG12)+4,8),IF(BP12=7,MID(CG12,FIND("INV ",CG12)+4,9)))))))),"")

BP12 is where I'm finding the length of the credit or return invoice number and CG12 is the location of my description where the original invoice number and line item reside.

Here's what I've run into. Since invoice numbers increment by 1, there are times where the credit or return invoice length could be different than the original invoice length.
For example, if the original invoice number was 9999 (4 digits), and I then credit or return that line item, the invoice number for the credit or return would be 5 digits instead of 4, which breaks my logic.

I tried your other formula you posted on 6/5/20:

=MID(CG12,FIND("INV ",CG12)+4,FIND(" ",MID(CG12,FIND("INV ",CG12)+4,255))-1)

...but the item# could be 1, 2 or 3 digits so this formula won't work either.

Do you have any other tricks up your sleeve?

 

[BobUmlas]

Probably, but I need the original examples to work from. PLease supply a few...

 

[jeff_cp]

Here are a few examples of the descriptions:

  (INV 1-1) IMPREZ 08 Int.10102 AT, (2.5L), w/o turbo; 3 boltshifter switch ??RECHECK,(12 pin p

INV 123-10  FPM TOUARE 08 Int.59325 Pump Assembly,(in tank), 4.2L, RH,FTWCAR - Pump Assembly, (in tank), 4.2L, RH

TRANSMISSION ASSY. CORE REFUND INV 13919-2  (INV 13919-2) TRANSMISSION ASSY.

INV 139801-110  HLP KIAFOR (Extra Sale) 18 Int.10027 M exico built (VIN 3, 1st digit), halogen, w/o LED Mexico built (VIN 3, 1st digit), halogen, w/o LED accent; RH

 

[BobUmlas]

This works with the 4 examples you just sent:
=SUBSTITUTE(MID(CG12,FIND("INV ",CG12)+4,FIND(" ",MID(CG12,FIND("INV ",CG12)+4,255))-1),")","")

 

[jeff_cp]

So far, so good. I'll do some further testing but everything looks good best I can tell. Thanks again!!!