1

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?

Improve this question
0

1 Answer 1

Reset to default
2

EDIT

You may try any one of these, as shown in the image below, edited since what JvdV Sir has mentioned does make sense.

• Formula used in cell B1

=REPLACE(A1,SUM(IFERROR(FIND({"DEV","BA"},A1),0)),255,"")

• Formula used in cell C1

=SUBSTITUTE(A1,LOOKUP(9^9,FIND({"BA","DEV"},A1),{"BA","DEV"}),"")

FORMULA_SOLUTION

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Beware SEARCH() is not case sensitive where FIND() is. What happens when the data is Devon Smith BA? I'd advise to make that small change just to prevent false positives. +
@JvdV Sir, agreed absolutely correct, I always use SEARCH function and that's my bad habit, I will take this note seriously moving forward! Thank you so much again!
@MayukhBhattacharya How is this formula choosing to get the data after instead of before?
@DreadPirateRoberts why what issue are you facing may i know, please because FIND Function is case sensitive as mentioned above by JvDv Sir, So it checks for the exact DEV or BA in the end
@MayukhBhattacharya Sorry, I realize now I'm trying do something quite a bit different. I need to do the inverse of this. "grep all of the after the match". I posted a new thread here - stackoverflow.com/questions/71439994/…
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.