Search code examples
searchreplacegoogle-sheets

Google Sheets multiple search and replace from a list

I am looking for a solution to search for certain strings in a Google Sheet and, when found, replace them with another string from a list in another sheet.

For better understanding, I prepared a Sheet for you:

https://docs.google.com/a/vicampo.de/spreadsheets/d/1mETtAY72K6ST-hg1qOU9651265nGq0qvcgvzMRqHDO8/edit?usp=sharing

So here's the exact task I want to achieve:

In every single cell in column A of sheet "Text", look for the strings given in column A in sheet "List" and, when found, replace it with the corresponding string in column B of the sheet "List".

See my Example: Look in cell A1 for the string "Lorem" and replace it with "Xlorem", then look for the string "Ipsum" and replace it with "Xipsum", then look for the string "amet" and replace it with "Xamet" then move on to cell B1 and start again looking for the strings...

I have tried different functions and managed to do this with a function for one cell. But how to do it in a loop?

Thanks everyone who is interested in helping out with this problem!

Solution

  • Although there must be 'nicer' solutions, a quick solution (as long is the number of cells with the words you want replaced is not too long), would be:

    =ArrayFormula(regexreplace(regexreplace(regexreplace(A1:A; List!A1; List!B1); List!A2; List!B2); List!A3; List!B3))