Excel Substitute given values and delete non given values

I have 3 columns named Old Model, New Model, & Obsolete Model. The New Model should be the output and is the New name from the Old Model table which is the input where as: AB should be IJ, CD should be KL, and EFshould be MN. The Obsolete Model containing GH should not be included in the output of column New Model.

Old Model     New Model    Obsolete Model
AB,CD,EF,GH   IJ,KL,MN     GH

I can simply use the Substitute excel formula for this one which goes like this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"AB","IJ"),"CD","KL"),"EF","MN")

The problem with this formula is that it will still show GH in the New Model column.

Can Anyone help me with this one? I have more than 1000 lines and within are different Old Model different New Model and different Obsolete Model

Is there a simple approch for this one without using VBA codes since the fili I am running is quite big and I'm affraid it might slow down the process.

Best Regards,

Answers


EDIT

Extract one old model from the cell like this:

=MID(A1,FIND("CD",A1),(len("CD")))

Result: AB,CD,EF,GH --> CD

Concatenate as many of those sequences as necessary to isolate all your old models.

=MID(A1,FIND("AB",A1),(len("AB")))&MID(A1,FIND("CD",A1),(len("CD")))&MID(A1,FIND("EF",A1),(len("EF"))) 

Then in a separate column, you an apply your original substitute formula to the result.


Add another substitute statement and substitute "GH" with nothing, "".

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"AB","IJ"),"CD","KL"),"EF","MN"), "GH", "")

Then to isolate "GH" in the obsolete column, just replace all the other values with nothing.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"AB",""),"CD",""),"EF",""), "GH", "")

Make sure to add commas to the strings to be replaced if you want to get rid of those also, i.e. "AB,", "CD,", "EF,".


Need Your Help

DataSource connections not getting released

spring datasource spring-batch apache-commons-dbcp transactionmanager

I am using DataSource and DataSourceTransactionManager spring beans and wiring them into JobRepository bean. Shouldn't one of these be lifecycle aware or have a close function to close the connection

Magento URL adding random string

string url magento

Currently have a Magento install running which seems to be printing #.UD3vymhSSYV at the end of every product URL. I assumed I could remove it using the htaccess file, however whenever this is done...