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?

Is it just the last 'word' you want (i.e. everything after the last space)? — Rory
– Rory, Commented Mar 11, 2022 at 14:31
@Rory I need to search on DEV or BA and fetch it along with all of the text that follows. In some cases the data will have space too. (working with old data) — DreadPirateRoberts
– DreadPirateRoberts, Commented Mar 11, 2022 at 14:41

Mayukh Bhattacharya · Accepted Answer · 2022-03-11 14:51:54Z

3

Try using this

• Formula used in cell B1

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

answered Mar 11, 2022 at 14:51

Mayukh Bhattacharya

29.2k9 gold badges31 silver badges46 bronze badges

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

Comments

JvdV · Accepted Answer · 2022-03-22 12:48:10Z

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.

edited Mar 22, 2022 at 12:48

answered Mar 22, 2022 at 12:42

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

5 Comments

Mayukh Bhattacharya Over a year ago

Nice One Sir!!!

Mayukh Bhattacharya Over a year ago

Sir I can't discriminate with any of those, I have only used those which you have shown in solutions sir, ! I shall follow what you share, because every single info i will find in your solutions. Last but not least I like Excel in every form :)

Mayukh Bhattacharya Over a year ago

Sir, in the formula above do we need [instance_num] --> 1 for SUBSTITUTE() Function ?

JvdV Over a year ago

@MayukhBhattacharya Yes, that is just to be sure we don't substitute trailing text by accident. It's not likely in OP's data, but I wanted to make it a more generalized answer. Imagine data like ABC DEV ABC 123. Without the 4th parameter initialized to '1' it would substitute the latter 'ABC ' too. Now, by definition TEXTBEFORE() will return exact string before 'DEV' and if we substitute it just once it will always work safely trimming the text to the left of 'DEV' only. It's also explained (with fewer words) in my answer.

Mayukh Bhattacharya Over a year ago

Yes Sir, thank you very much, I just tested with this Dan Smith DEV001 Dan Smith & used the formula as you stated it worked as expected but if i remove the 1 then it removes the trailing Dan Smith as well but only if there is a space after the second one. Very nice, So basically TEXTBEFORE() is an advanced form of FIND() Function. So using 1 is perfect & correct. Thanks again for the share of knowledge!

Collectives™ on Stack Overflow

Excel Remove text before string for multiple possible strings

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related