问题描述
假设我有一个由 = unique()函数生成的文本值列(名为 Data ).此外,还有一系列模式可以查找和替换(查找和替换列).
Suppose I've got a text values column (named Data), generated by =unique() function. Also, there is an array of patterns to find and replace for (Find and Replace columns).
我应该使用哪个公式来扫描数据中的每个单元格以查找 Find 中的多个模式,并在匹配的情况下将其替换?
Which formula should I use to scan each cell in Data for multiple patterns in Find and replace it, if match?
Data Find Replace Result
1 a c z a
2 b f y b
3 c e x z
4 d d
5 e x
6 c z
尝试了 = SUBSTITUTE()和 = IF()函数,但是当我设置模式数组而不是单个模式时,它失败了.
Tried =SUBSTITUTE() and =IF() functions, but it fails, when I set an array of patterns, instead of single one.
推荐答案
如果表位于A1:E7范围内,请尝试以下公式
If the table you is in range A1:E7, try this formula
=TRANSPOSE(SPLIT(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(ARRAYFORMULA(CONCATENATE($B$2:$B$7&"|")),$C$2,$D$2),$C$3,$D$3),$C$4,$D$4),"|"))
您可以在较早的帖子中进一步了解 ,并且 Google文档论坛.
You can read further about this in an older post and google docs forum.
这篇关于查找并替换为多种模式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!