Extract text using RegEx without VBA

Question

I have the following text, and I'd like to pull the PO number. Sometimes it starts with the words PO, sometimes it doesn't. If it doesn't, then PO number will start with either 901 or 902.

PO or PO Number starts may start in the beginning, middle or at the end. There'll always be a space "delimter" in the beginning and/or at the end

sometexthere sometext 123 90112345 sometext
some text PO90199 sometexthere
some 90 1 901999999 sometext
sometexthere 902123456
sometexthere PO90212334
PO902101010 sometext here
9019394 sometext here

What I'd like to extract would be

90112345
PO90199
901999999
902123456
PO90212334
PO902101010
9019394

Answer 1

Use BYROW to iterate the column and FILTERXML to split the TEXT. Then use FILTER to return the correct item from the FILTERXML array:

=BYROW(A1:A7,LAMBDA(a,LET(txt,FILTERXML("<t><s>"&SUBSTITUTE(a," ","</s><s>")&"</s></t>","//s"),FILTER(txt,ISNUMBER(MATCH(LEFT(txt,3),{"PO9","901","902"},0))))))

---

Notes:

Note 1: FILTERXML is only available on the PC versions.

Note 2: Once TEXTSPLIT is introduced we can avoid the FILTERXML:

=BYROW(A1:A7,LAMBDA(a,LET(txt,TEXTSPLIT(a," "),FILTER(txt,ISNUMBER(MATCH(LEFT(txt,3),{"PO9","901","902"},0))))