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/
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(
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.
-----------------------------------------------
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/
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(
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:
Many thanks Yogi.
ReplyDeleteI'll look into the ArrayFormula(regexreplace()) functions.
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