Skip to main content
Web Applications

Return to Question

Tweeted twitter.com/#!/StackWebApps/status/358175603008081921
corrected spelling
Source Link
Adam Badura
Adam Badura
  • 373
  • 1
  • 3
  • 8

Is isit possible to write such a formula that it will substitute in a text value all occurrences of few values with their replacements?

For example I would like to substitute all occurrences of aaa with 1 and bbb with 2. So that text aaabbbaaa will be changed to 121.

I have prepared an example spreadsheet which also has write access so feel free to add a column with your formula and in the answer just explain it. Also you can add new test cases.

In my “real world” case the look-up table is quite large (45 values) but could be considered “fixed”. So yes, I could just make a formula consisting of 45 nested SUBSTITUTE but such solution is obviously bad.

Also my real case is not as tricky as the example so there is no risk that replacement text will in any way match (even in parts) any of the values to be substituted. (In the example ddd is replaced with a which matches partially value aaa!) So if that causes problems you might skip the “feature”.

And finally in my real case I'm lucky enough to be able to split the text into parts which contain no more than one value to be replaced (and possibly some other character data!). The result could be then joined back into a single string. If that would make it any easier you could use that as well.

I tried using both SUBSTITUTE and REGEXREPLACE where text to be found and replacement text are arrays of values but that results in an array of results rather than multiple substitutions.

I tried also using REGEXREPLACE with VLOOKUP in replacement text that looked up for "$1" (the matched text) but that didn't work. The "$1" within VLOOKUP within REGEXREPLACE was treated literally rather than interpreted as matched text (as it would be if it was used in REGEXREPLACE directly).

Is is possible to write such a formula that it will substitute in a text value all occurrences of few values with their replacements?

For example I would like to substitute all occurrences of aaa with 1 and bbb with 2. So that text aaabbbaaa will be changed to 121.

I have prepared an example spreadsheet which also has write access so feel free to add a column with your formula and in the answer just explain it. Also you can add new test cases.

In my “real world” case the look-up table is quite large (45 values) but could be considered “fixed”. So yes, I could just make a formula consisting of 45 nested SUBSTITUTE but such solution is obviously bad.

Also my real case is not as tricky as the example so there is no risk that replacement text will in any way match (even in parts) any of the values to be substituted. (In the example ddd is replaced with a which matches partially value aaa!) So if that causes problems you might skip the “feature”.

And finally in my real case I'm lucky enough to be able to split the text into parts which contain no more than one value to be replaced (and possibly some other character data!). The result could be then joined back into a single string. If that would make it any easier you could use that as well.

I tried using both SUBSTITUTE and REGEXREPLACE where text to be found and replacement text are arrays of values but that results in an array of results rather than multiple substitutions.

I tried also using REGEXREPLACE with VLOOKUP in replacement text that looked up for "$1" (the matched text) but that didn't work. The "$1" within VLOOKUP within REGEXREPLACE was treated literally rather than interpreted as matched text (as it would be if it was used in REGEXREPLACE directly).

Is it possible to write such a formula that it will substitute in a text value all occurrences of few values with their replacements?

For example I would like to substitute all occurrences of aaa with 1 and bbb with 2. So that text aaabbbaaa will be changed to 121.

I have prepared an example spreadsheet which also has write access so feel free to add a column with your formula and in the answer just explain it. Also you can add new test cases.

In my “real world” case the look-up table is quite large (45 values) but could be considered “fixed”. So yes, I could just make a formula consisting of 45 nested SUBSTITUTE but such solution is obviously bad.

Also my real case is not as tricky as the example so there is no risk that replacement text will in any way match (even in parts) any of the values to be substituted. (In the example ddd is replaced with a which matches partially value aaa!) So if that causes problems you might skip the “feature”.

And finally in my real case I'm lucky enough to be able to split the text into parts which contain no more than one value to be replaced (and possibly some other character data!). The result could be then joined back into a single string. If that would make it any easier you could use that as well.

I tried using both SUBSTITUTE and REGEXREPLACE where text to be found and replacement text are arrays of values but that results in an array of results rather than multiple substitutions.

I tried also using REGEXREPLACE with VLOOKUP in replacement text that looked up for "$1" (the matched text) but that didn't work. The "$1" within VLOOKUP within REGEXREPLACE was treated literally rather than interpreted as matched text (as it would be if it was used in REGEXREPLACE directly).

Source Link
Adam Badura
Adam Badura
  • 373
  • 1
  • 3
  • 8

Multiple substitutions in a single text

Is is possible to write such a formula that it will substitute in a text value all occurrences of few values with their replacements?

For example I would like to substitute all occurrences of aaa with 1 and bbb with 2. So that text aaabbbaaa will be changed to 121.

I have prepared an example spreadsheet which also has write access so feel free to add a column with your formula and in the answer just explain it. Also you can add new test cases.

In my “real world” case the look-up table is quite large (45 values) but could be considered “fixed”. So yes, I could just make a formula consisting of 45 nested SUBSTITUTE but such solution is obviously bad.

Also my real case is not as tricky as the example so there is no risk that replacement text will in any way match (even in parts) any of the values to be substituted. (In the example ddd is replaced with a which matches partially value aaa!) So if that causes problems you might skip the “feature”.

And finally in my real case I'm lucky enough to be able to split the text into parts which contain no more than one value to be replaced (and possibly some other character data!). The result could be then joined back into a single string. If that would make it any easier you could use that as well.

I tried using both SUBSTITUTE and REGEXREPLACE where text to be found and replacement text are arrays of values but that results in an array of results rather than multiple substitutions.

I tried also using REGEXREPLACE with VLOOKUP in replacement text that looked up for "$1" (the matched text) but that didn't work. The "$1" within VLOOKUP within REGEXREPLACE was treated literally rather than interpreted as matched text (as it would be if it was used in REGEXREPLACE directly).