Find and extract a substring that meets a specific character format

[SuttieB2404]

Hi I'm trying to write a formula that extracts an invoice reference that is either two capital letters followed by 4 or five numbers from a variable string in a cell.
i.e extract 'AB1234' from 'Invoice 2222 Date: 01/01/2002 AB1234 Ref: A123456'

I found a post that counted double capitals that may be able to modify?
=SUM((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1)-1)),1))>=64)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1)-1)),1))<=90)
*(CODE(MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1))>=64)*(CODE(MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1))<=90))

Cheers
Stu

 

[Sufiyan97]

The formula is taken from one of the solution by Fluff.

If it is always in 3rd positin from last word

Try

Excel Practice 05.22.2023.xlsx
	A	B
1	Invoice 2222 Date: 01/01/2002 AB1234 Ref: A123456	AB1234
2
3
Sheet3

Cell Formulas
Range	Formula
B1	B1	=FILTERXML("<k><m>"&SUBSTITUTE(A1," ","</m><m>")&"</m></k>","//m[.!=number()][position()=last()-2]")

 

[SuttieB2404]

The formula is taken from one of the solution by Fluff.

If it is always in 3rd positin from last word

Try

Excel Practice 05.22.2023.xlsx
	A	B
1	Invoice 2222 Date: 01/01/2002 AB1234 Ref: A123456	AB1234
2
3
Sheet3

Cell Formulas
Range	Formula
B1	B1	=FILTERXML("<k><m>"&SUBSTITUTE(A1," ","</m><m>")&"</m></k>","//m[.!=number()][position()=last()-2]")

Thanks for the reply, unfortunately the reference could appear anywhere in the string hence why I thought it might be better to search for the substring format/length

 

[Georgiboy]

Are you still using excel 2016?

 

[Peter_SSs]

You could consider employing a user-defined function like this. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function InvNo(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "(^|[^A-Z])([A-Z]{2}\d{4,5})(\D|$)"
    If .Test(s) Then InvNo = .Execute(s)(0).SubMatches(1)
  End With
End Function

SuttieB2404.xlsm
	A	B
1	Invoice 2222 Date: 01/01/2002 AB1234 Ref: A123456	AB1234
2	Other Text
3
4	Last Inv BB23765	BB23765
5	Last Inv aB23766
6	ABC12345 or DE1234567 or FG76543	FG76543
Sheet1

Cell Formulas
Range	Formula
B1:B6	B1	=InvNo(A1)

 

[SuttieB2404]

You could consider employing a user-defined function like this. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function InvNo(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "(^|[^A-Z])([A-Z]{2}\d{4,5})(\D|$)"
    If .Test(s) Then InvNo = .Execute(s)(0).SubMatches(1)
  End With
End Function

SuttieB2404.xlsm
	A	B
1	Invoice 2222 Date: 01/01/2002 AB1234 Ref: A123456	AB1234
2	Other Text
3
4	Last Inv BB23765	BB23765
5	Last Inv aB23766
6	ABC12345 or DE1234567 or FG76543	FG76543
Sheet1

Cell Formulas
Range	Formula
B1:B6	B1	=InvNo(A1)

Unfortunately it needs to be without VBA
I'd have been OK otherwise
Stu

 

[SuttieB2404]

Are you still using excel 2016?

Nah, on 365 now.. Just updated my profile
Stu

 

[Georgiboy]

If the Ref number is always after 'Ref: ' then maybe:

Book1
	A	B
1	Invoice 2222 Date: 01/01/2002 AB1234 Ref: A123456	A123456
2	Invoice 2222 Date: 01/01/2002 AB1234 Ref: A123457 & Cheese on Toast	A123457
3	Ref: A123457 Invoice 2222 Date: 01/01/2002 & Cheese on Toast	A123457
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=LET(ta,TEXTAFTER(A1:A3,"Ref: "),IF(ISNUMBER(FIND(" ",ta)),TEXTBEFORE(ta," "),ta))
Dynamic array formulas.

 

[Fluff]

If the value is always after the date, another option is

Fluff.xlsm
	A	B
1
2	Invoice 2222 Date: 01/01/2002 AB1234 Ref: A123456	AB1234
Main

Cell Formulas
Range	Formula
B2	B2	=FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m[contains(.,'/') ]/following::m[1]")

 

[SuttieB2404]

If the value is always after the date, another option is

Fluff.xlsm
	A	B
1
2	Invoice 2222 Date: 01/01/2002 AB1234 Ref: A123456	AB1234
Main

Cell Formulas
Range	Formula
B2	B2	=FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m[contains(.,'/') ]/following::m[1]")

No, the full string is received from various sources and is not constant but always contains the substring I need to extract as either TTNNNN or TTNNNNN