Friday, December 23, 2011

yogi_Replace A Substring With Another Substring (Not Quite)

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?

Hi Yogi, thanks for quick response.

I have created
https://docs.google.com/spreadsheet/ccc?key=0Asd7WkVXqKmddGFmU1RUOUdxT0kwQTRQZWpDWkJxeGc
which is an example of the task that I am seeking to solve. In this example I want to take all the 'Sample names' in the 'Sample data' sheet and replace with a null character any occurrence of any of the single characters within the following double quotes: "abcdefghijklmnopqrstuvwxyz, '-".

This example spreadsheet achieves this by using formulae such as.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2 ; "a" ; "") ; "b" ; "") ; "c" ; "") ; "d" ; "") ; "e" ; "") ; "f" ; "") ; "g" ; "") ; "h" ; "") ; "i" ; "") ; "j" ; "") ; "k" ; "") ; "l" ; "") ; "m" ; "") ; "n" ; "") ; "o" ; "") ; "p" ; "") ; "q" ; "") ; "r" ; "") ; "s" ; "") ; "t" ; "") ; "u" ; "") ; "v" ; "") ; "w" ; "") ; "x" ; "") ; "y" ; "") ; "z" ; "") ; "," ; "") ; " " ; "") ; "'" ; "") ; "-" ; "")
Not pretty. So I am seeking a better way of achieving this. fyi the formula is generated by the 2nd sheet of the spreadsheet. This particular example yields the initials of the 'sample names' if these are all consistently capitalised. I would really love a more general solution to this problem, e.g. to be able to have two columns in a sheet: 'text to find' and 'text to substitute with', and then a way to get all rows in that sheet to be used to do effect the substitutions. In the sample names initials example, the 'text to substitute with' would all be blank cells, but in other situations I have wanted this same capability but with substitution of non-null strings.
Any help would be appreciated, thanks.

-----------------------------------------------
if I have understood it correctly, in my following solution to the problem I used RegexReplace function in a single array formula: