If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
Thread Tools | |
Display Modes | |
|
|