Google表格可以从列表中进行多次搜索和替换 [英] Google Sheets multiple search and replace from a list

问题描述

我正在寻找一种在Google表格中搜索某些字符串的解决方案，并在找到时将其替换为另一张工作表中的另一个字符串.

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:

在工作表文本"的A列的每个单元格中，查找工作表列表"的A列中给出的字符串，找到后，将其替换为工作表"B列"中的相应字符串列表".

请参阅我的示例:在单元格A1中查找字符串"Lorem"，并将其替换为"Xlorem"，然后查找字符串"Ipsum"，并将其替换为"Xipsum"，然后查找字符串"amet"，并将其替换为"Xamet"，然后移至单元格B1并再次开始寻找字符串...

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?

感谢有兴趣帮助此问题的所有人！

推荐答案

尽管必须有更精细"的解决方案，但快速的解决方案(只要要替换的单词的单元格数量不太长)，就会是:

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))

这篇关于Google表格可以从列表中进行多次搜索和替换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！