Replace Function that uses lookup table?
 

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!

Replace Function that uses lookup table?
 

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


"msnyc07" wrote:

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!