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)+5,8)

 

[BobUmlas]

=MID(O4,FIND("INV ",O4)+4,8)

(saw one without the "(". )

 

[jeff_cp]

Thanks Bob. I applied your formula and copied it down the column and that got them. However, I just noticed that some of the fields I'm extracting from have no invoice-item# in them so how would I modify that formula to return "" if the invoice-item# isn't there?

Also, would this formula only work with a six digit invoice#? I believe this system allows up to seven digit invoice numbers. Sorry, I just thought about that.

 

[jeff_cp]

Ok I can simply wrap your formula in iferror and that will return my "" but what about the invoice length?

 

[jeff_cp]

Ok hold up. I think I got this. One of the available fields in the database is the invoice number so I can use that to get my field length and modify your formula to use one vs the other if the invoice number in the database is 6 or 7 digits. What I'm extracting from the fields is the original invoice# because there's no field in the database for it. I'll post back if I need any more help. Thanks again!!!

 

[jeff_cp]

Here's the finished formula and it seems to be working perfect. I added a field in column I to find the length of the invoice number field and modified the formula to check it.
=IFERROR(IF(I4=6,MID(O4,FIND("INV ",O4)+4,8),IF(I4=7,MID(O4,FIND("INV ",O4)+4,9))),"")

 

[sandy666]

another approach with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TBD = Table.AddColumn(Source, "TBD", each Text.BetweenDelimiters([RAW], "INV ", " "), type text),
    Filter = Table.SelectRows(TBD, each ([TBD] <> "")),
    Replace = Table.ReplaceValue(Filter,")","",Replacer.ReplaceText,{"TBD"}),
    TSC = Table.SelectColumns(Replace,{"TBD"})
in
    TSC

 

[jeff_cp]

Thank you Sandy.

 

[sandy666]

You are welcome