Excel Remove text before string for multiple possible strings

Question

With Excel, I need to find and remove some text from all cells in a column. Using the example below I need get all instances of DEV* and BA* into another column.

Example data in a column:
Data
Dan Smith DEV001
Bob Jones BA005
Bob Jones2 BA 005

Needed Result
DEV001
BA005
BA 005

This example works partially but not with multiple possible matches.

=TRIM(RIGHT(A2, LEN(A2) - SEARCH("DEV", A2)))

How can this be done with multiple possible matches?

Answer 1

Try using this

• Formula used in cell B1

=REPLACE(A1,1,MAX(IFERROR(FIND({"DEV","BA"},A1),""))-1,"")