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,"")

Answer 2

Here is something to consider for those using ms365 with access to TEXTBEFORE():

Formula in B1:

=SUBSTITUTE(A1,TEXTBEFORE(A1,{"DEV","BA"}),"",1)

• TEXTBEFORE() - will look (case sensitive) for either 'DEV' or 'BA' and will return the substring before the 1st occurence of any of these two;
• SUBSTITUTE() - will replace (also case sensitive) this returned substring with nothing. And to make sure we won't substitute unwanted parts after the lookup value we only replace the 1st occurence.