With Excel, I need to find and remove some text from all cells in a column. Using the example below I need to remove all instances of DEV or BA in this column.
Example data in a column:
Dan Smith DEV
Bob Jones BA
This example work but not with multiple possible matches.
=LEFT(A1,FIND({"DEV"},A1)-1)
How can this be done with multiple possible matches?
>Solution :
You may try any one of these, as shown in the image below,
Formula used in cell B1
=REPLACE(A1,SUM(IFERROR(SEARCH({"DEV","BA"},A1),0)),255,"")
Formula used in cell C1
=SUBSTITUTE(A1,LOOKUP(9^9,SEARCH({"BA","DEV"},A1),{"BA","DEV"}),"")
