Replace Function that uses lookup table?

[msnyc07]

I have a large (50k+records) spreadsheet I've been 'cleaning' using some
basic cell functions that look for specific strings and then remove or
substitute, using mostly if(isnumber(search("String", Cell#) and things like
left, right, substitute.

However these are getting kind of long as the lists of words to
remove/replace is getting large.

At it's simplest I'd love to do something that effectively does:

In This Cell
If the String Contains <AnyWordsInThisTable/Row/Etc>
Remove that Word

Even Better would be if I had a two column sheet (e.g. WordsSheet) and could
do

If this String Contains Any of the Words in WordSheet:ColumnA
Replace with WordSheet;ColumnB

Ideally in either/both cases I could specify Whole or Partial Word

Any ideas appreciated, my cell formulas are getting quite clunky and hard to
both create and troubleshoot!

Thanks in advance!

 

[Eva]

Hi
Create a tab called List, in Column A put the description in col B - what
you need as the result of the search

Then paste the following formula in your data
=VLOOKUP(INDEX(List!A$2:A$200,MATCH(1,--ISNUMBER(SEARCH(List!A$2:A$200,A2)),0)),List!A:B,2,FALSE)

It is an array formula (click Ctrl+shift+enter)
--

Please click "yes" if this post helped you!

Greatly appreciated

Eva