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屋！