Extract text substring meeting specific Criteria from a Cell

I have a list of patients in a cell it can contain their name and a Reference Number and other information. The reference number may not be in a cell, but if it exists it follows the pattern Letter, Letter, number, Number, Number, Number.
In the purely fictitious example below I have highlighted the substrings I would wish to extract in red. which I would populate in the column to the right

John Smith EN8172
Joe Bloggs ZY9096
Betty boos BA3698
Helen Conner Tomkins AI9352 - LAT in the morning
Ronal McDonald- AV6943
Colin booth XB5465
Martha Maddon BB8842 - Aspiration
Geoff Hillman
O'Reilyl CM1707

I suspect the answer will be via VBA (a function?), hence posting here, but it could be a formula


Thanks for any assistance that can be provided

Try this:

=IFERROR(MID(A1,MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},A1)),"",FIND({1;2;3;4;5;6;7;8;9;0},A1)))-2,6),"")

Originally Posted by Glenn Kennedy

63FD... that's nice!!

Thanks! And thank you for the rep!

Thank you for the fast responses. Whilst not matching the mask, it provides me with a perfectly workable solution for my needs. Thanks 63FD and Glenn

Glad we could help. Thanks for the rep!

Try this:

Please Login or Register  to view this content.

Array formula, enter with Ctrl+Shift+Enter.