Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP function syntax across a table? | Excel Worksheet Functions | |||
lookup and Replace with value | Excel Worksheet Functions | |||
Formula to replace a Pivot table function | Excel Discussion (Misc queries) | |||
Variable Table Array in Lookup Function | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |