Extract all 5 character alphanumerics in a string that where the 1st char is a letter, and the 2nd char is a number

[andrewmshoe]

I found a few related questions, but waasn't sure how to modify the script for my purposes.
E.g: Extract multiple 5-digit numbers from alphanumeric text

String	Desired Output
Hello there P0D1C, im great,% U1212	P0D1C U1212
My name is P6717 and I happy	P6717

They will always start with a P or U. Also, there may be long strings where 10 or more of these alphanumerics need to get extracted.

 

[andrewmshoe]

Try this for formula option.

Book1
	A	B
1	Hello there P0D1C, im great,% U1212	P0D1C U1212
2	My name is P6717 and I happy	P6717
3	This has both upper P6231 and lower p12312, plus U123Ud, minus u543d0d	P6231 p12312 U123Ud u543d0d
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=LET(t,TEXTSPLIT(A1,{" ",","},,1),x,LEFT(t,1),TEXTJOIN(" ",,FILTER(t,((x="P")+(x="U"))*(ISNUMBER(--MID(t,2,1))))))

This is really interesting and works for 95% of the dataset I have. My input data is quite messy so there are a number of edge cases, and I narrowed one down here to show a few issues:

...evehile.P0MC4 and P1EUABoth codes …

P1EUABoth

This method would be preferred as I'd like to convert this all to DAX for PowerBI at some point and this would be a much simpler starting point.

 

[Cubist]

If the delimiters around the code are limited to space, commas and periods, you can try this. You can add more delimiters to this part of the formula as needed

=LET(t,TEXTSPLIT(A1,{" ",",","."},,1),x,LEFT(t,1),TEXTJOIN(" ",,FILTER(LEFT(t,5),((x="P")+(x="U"))*(ISNUMBER(--MID(t,2,1))))))

Book1
	A	B
1	Hello there P0D1C, im great,% U1212	P0D1C U1212
2	My name is P6717 and I happy	P6717
3	This has both upper P6231 and lower p12312, plus U123Ud, minus u543d0d	P6231 p1231 U123U u543d
4	...evehile.P0MC4 and P1EUABoth codes …	P0MC4 P1EUA
Sheet3

Cell Formulas
Range	Formula
B1:B4	B1	=LET(t,TEXTSPLIT(A1,{" ",",","."},,1),x,LEFT(t,1),TEXTJOIN(" ",,FILTER(LEFT(t,5),((x="P")+(x="U"))*(ISNUMBER(--MID(t,2,1))))))

 

[andrewmshoe]

If the delimiters around the code are limited to space, commas and periods, you can try this. You can add more delimiters to this part of the formula as needed

=LET(t,TEXTSPLIT(A1,{" ",",","."},,1),x,LEFT(t,1),TEXTJOIN(" ",,FILTER(LEFT(t,5),((x="P")+(x="U"))*(ISNUMBER(--MID(t,2,1))))))

Book1
	A	B
1	Hello there P0D1C, im great,% U1212	P0D1C U1212
2	My name is P6717 and I happy	P6717
3	This has both upper P6231 and lower p12312, plus U123Ud, minus u543d0d	P6231 p1231 U123U u543d
4	...evehile.P0MC4 and P1EUABoth codes …	P0MC4 P1EUA
Sheet3

Cell Formulas
Range	Formula
B1:B4	B1	=LET(t,TEXTSPLIT(A1,{" ",",","."},,1),x,LEFT(t,1),TEXTJOIN(" ",,FILTER(LEFT(t,5),((x="P")+(x="U"))*(ISNUMBER(--MID(t,2,1))))))

I can't thank you enough! I will check for other problematic delimiters and add as needed.

 

[andrewmshoe]

Can I select more than 1 answer as solution? Also, I'd like to "buy a coffee" for you guys if that sort of thing is allowed/you have something set up for that.

 

[Cubist]

You're welcome.
You can see this thread for more details about marking a solution, but you can only choose one.
We're all volunteers here so no repayment is needed. A thank you is sufficient.