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

 

[Georgiboy]

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

Think I have extracted the wrong number! Ignore my last contribution.

 

[Peter_SSs]

See if this would do it.

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

Cell Formulas
Range	Formula
B1:B6	B1	=LET(c,CODE(MID(A1,SEQUENCE(LEN(A1)),1)),s,CONCAT(3,IF(ABS(c-77.5)<13,1,IF(ABS(c-52.5)<5,2,3)),3), fr,IFERROR(FIND(31122223,s),0),fv,IFERROR(FIND(311222223,s),0),IF(fr,MID(A1,fr,6),IF(fv,MID(A1,fv,7),"")))

 

[SuttieB2404]

See if this would do it.

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

Cell Formulas
Range	Formula
B1:B6	B1	=LET(c,CODE(MID(A1,SEQUENCE(LEN(A1)),1)),s,CONCAT(3,IF(ABS(c-77.5)<13,1,IF(ABS(c-52.5)<5,2,3)),3), fr,IFERROR(FIND(31122223,s),0),fv,IFERROR(FIND(311222223,s),0),IF(fr,MID(A1,fr,6),IF(fv,MID(A1,fv,7),"")))

Thank you, it works beautifully..
I've set some homework to understand LET and SEQUENCE as I've only recently switched from 2016 ..

 

[Georgiboy]

Here is another option - as i was still working with it:

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

Cell Formulas
Range	Formula
B1:B3	B1	=LET(ts,TEXTSPLIT(A1," "), lts,"ABCDEFGHIJKLMNOPQRSTUVWXYZ", IFERROR(TAKE(FILTER(ts,(NOT(ISERROR(FIND(LEFT(LEFT(ts,2),1),lts)))*(NOT(ISERROR(FIND(MID(LEFT(ts,2),2,1),lts)))))),,-1),""))

 

[Peter_SSs]

Thank you, it works beautifully..

You're welcome. Thanks for the follow-up.

Here is another option

I don't know what is possible with the OP's data, but that is not robust to extract the requested text format. For example:

SuttieB2404.xlsm
	A	B
8	Invoice 2222 Date: 01/01/2002 AB1234 Ref: JD Smith	JD
Sheet1

Cell Formulas
Range	Formula
B8	B8	=LET(ts,TEXTSPLIT(A8," "), lts,"ABCDEFGHIJKLMNOPQRSTUVWXYZ", IFERROR(TAKE(FILTER(ts,(NOT(ISERROR(FIND(LEFT(LEFT(ts,2),1),lts)))*(NOT(ISERROR(FIND(MID(LEFT(ts,2),2,1),lts)))))),,-1),""))

 

[Georgiboy]

Hi @Peter_SSs, thanks for the stress test, I was working with the sample data, if that was part of the sample data, I would have added in a small check to see if the third character was numeric as below. We don't know what is possible or what is coming, the OP may want to look for 6 numbers following two capital letters at some point. I know what is stated in the OP but just playing devil’s advocate as there are any number of potentials for the data and different solutions will offer various pros and cons:

Book1
	A	B	C
1	ABC123456 or DE123456 or FG123456		FG123456
2	Invoice 2222 Date: 01/01/2002 AB1234 Ref: JD Smith	AB1234	AB1234
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=LET(c,CODE(MID(A1,SEQUENCE(LEN(A1)),1)),s,CONCAT(3,IF(ABS(c-77.5)<13,1,IF(ABS(c-52.5)<5,2,3)),3), fr,IFERROR(FIND(31122223,s),0),fv,IFERROR(FIND(311222223,s),0),IF(fr,MID(A1,fr,6),IF(fv,MID(A1,fv,7),"")))
C1:C2	C1	=LET(ts,TEXTSPLIT(A1," "), lts,"ABCDEFGHIJKLMNOPQRSTUVWXYZ", IFERROR(TAKE(FILTER(ts,(NOT(ISERROR(FIND(LEFT(LEFT(ts,2),1),lts)))*(NOT(ISERROR(FIND(MID(LEFT(ts,2),2,1),lts))))*(ISNUMBER(--MID(ts,3,1))))),,-1),""))

 

[Peter_SSs]

just playing devil’s advocate

.. continuing that theme, this should still be AB1234

SuttieB2404.xlsm
	A	B	C
2	Invoice 2222 Date: 01/01/2002 AB1234 Ref: JD3	AB1234	JD3
Sheet3

Cell Formulas
Range	Formula
B2	B2	=LET(c,CODE(MID(A2,SEQUENCE(LEN(A2)),1)),s,CONCAT(3,IF(ABS(c-77.5)<13,1,IF(ABS(c-52.5)<5,2,3)),3), fr,IFERROR(FIND(31122223,s),0),fv,IFERROR(FIND(311222223,s),0),IF(fr,MID(A2,fr,6),IF(fv,MID(A2,fv,7),"")))
C2	C2	=LET(ts,TEXTSPLIT(A2," "), lts,"ABCDEFGHIJKLMNOPQRSTUVWXYZ", IFERROR(TAKE(FILTER(ts,(NOT(ISERROR(FIND(LEFT(LEFT(ts,2),1),lts)))*(NOT(ISERROR(FIND(MID(LEFT(ts,2),2,1),lts))))*(ISNUMBER(--MID(ts,3,1))))),,-1),""))