yogi_Replace Substrings In Rows of A Column With Other Substrings From A Lookup Table

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user flimzitree said
replace substring with substring
B2: =SUBSTITUTE( TRIM(A2) ; CHAR(34) ; "")
The SUBSTITUTE function lets me replace a substring with another string ... but what if I want to have 20 different substitutions to make to every one of hundreds of string cells in a speadsheet column?
e.g.
A1 = "sdvkcvhjghgfkjhgvjkhbljh"
I want to substitute every occurrence of 'adsfg' with 'kjhgk', every occurrence of 'bdfgh' with 'fgsdg' ... etecetera for 20 substitutions.
I could create a complex nested set of SUBSTITUTE functions, but is there a better way of doing this? Maybe one that looks up the substitution terms from another sheet in case I want to change them without changing all the nested equations?
------
But supposing instead of replacing single characters with null characters I wanted to look up a list of substrings to substitute with a corresponding list of strings. e.g. in the string '/dog//cat//snake//parrot/' I want to substitute every occurrence of '/dog/' with '/mammal/', every occurrence of '/snake/' with '/reptile/' etc. I would like these substitution rules to come from a table, since there may be dozens of 'rules' to apply, and the same rules to be applied to hundreds of cells in a column.
:) Dom
--------------------------------
following is a solution to the problem: