Macro to "clean" a column

[julie_drymo]

Hi everyone,

I need a little help to "clean" an excel file. I had started doing it manually but it takes too much time

Here it is : I have this worksheet with 2 tabs, 1st tab is "filter", 2nd tab is "BDD".

The list I wish to "clean" is in the column B of the "BDD" tab. I need to clean it according to 2 rules and have the result appear in column D.

rule #1 : each cell end by "Gé : xxxx" or ": xxxx". I wish to take that off.
example1 : "I went shopping today Gé : blop" -> "I went shopping today"

rule #2 : somes cells contain word or expression I wish to remove as well. The list of the word to remove automatically is in the column A of the 1st tab "filter
example2 : the word "car" appears in the "filter" column, then "this car is really expensive : a lot of money" -> "this is really expensive"

/!\ the "filter" column can sometimes contain 2 words in a cell, if "car" and "blue car" are in the list, then "this blue car is really expansive" -> "this is really expansive"

=> And finally, to complicate things a little bit, each word to be removed can be potentially followed by the words "LP", "L.P." or "L.P". I need that removed too only when it follows directly the word in the "filter" column
example3 : "the blue car LP is really expansive L.P : 100 bucks" -> "the is really expansive L.P"

Thanks a lot if you can help me out on this

++
Julie

[StephenR]

I suggest you post a sample workbook, showing representative data and desired results.

[snb]

Please Login or Register  to view this content.

[julie_drymo]

Thanks a lot Snb, it works !!

I just noticed a small problem concerning the "LP", "L.P" and "L.P." : they are sometimes removed correctly, sometimes not.

Lets simplify the process : it is possible to have them removed systematically anywhere they appear ?

Thanks a lot

++
Julie

[julie_drymo]

Anybody ?
I just need the previous code modified so that all these "LP", "L.P." and "L.P" disappear.

Thanks a lot if you can help me on this.

++
Julie