我该如何在Excel中组合多个嵌套的替换函数？

Question

我正在尝试设置一个函数来重新格式化一个字符串，稍后将连接该字符串。一个示例字符串看起来像这样：

Standard_H2_W1_Launch_123x456_S_40K_AB

有时候"S"不存在，有时候"40K"变成了"60K"或者没有，还有"_AB"也可以是"_CD"或"_EF"。 最后，所有的下划线都需要改成连字符。最终产品应该如下所示：

Standard-H2-W1-Launch-123x456-

我有四个函数，如果依次运行，就能处理所有这些问题：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"_AB","_"),"_CD","_"),"_EF","_")

=SUBSTITUTE(SUBSTITUTE(B2,"_40K",""),"_60K","")

=SUBSTITUTE(C2,"_S_","_")

=SUBSTITUTE(D2,"_","-")

我尝试过多种方法将这些内容合并成一个函数，但是我对Excel的这个级别还比较陌生，所以不知道该怎么做。有没有办法将所有内容组合在一起，以便在一个单元格中按顺序执行？

- samanthathyme

Answer 1

为了简单地将它们组合在一起，您可以像这样将它们全部放在一起：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"_AB","_"),"_CD","_"),"_EF","_"),"_40K",""),"_60K",""),"_S_","_"),"_","-")

注意，这可能会超过旧版本Excel的7个嵌套语句限制。我在Excel 2010中测试。

---

另一种方法是利用Left和Right函数。

假设末尾的更改数据始终存在且长度为8个字符。

=SUBSTITUTE(LEFT(A2,LEN(A2)-8),"_","-")

这将实现相同的结果字符串

---

如果要剥离的尾部字符不总是以8个字符结尾，您可以搜索“_S”并获取当前位置。尝试这个：

=SUBSTITUTE(LEFT(A2,FIND("_S",A2,1)),"_","-")

- Automate This

---

非常感谢！在发布问题之前，我已经尝试了您提供的第一个选项。我一定是犯了错误，因为我无法让那个解决方案起作用。在使用您的字符串后，我终于能够使用组合函数了。谢谢！ - samanthathyme

Answer 2

• 在字符串中嵌套SUBSTITUTE()函数可能会很麻烦，但是总是有办法解决：

- player0

Answer 3

感谢Werner提出的分解公式的想法！

使用Alt+Enter可以将复杂的替代公式的每个部分放在不同的行中：这样它们就更容易跟踪，并且当按下Enter时自动排列在一起。

只需确保您有足够的结束语句来匹配单元格引用的substitute（两侧的行数。

如此例所示：

=
substitute(
substitute(
substitute(
substitute(
B11
,"(","")
,")","")
,"[","")
,"]","")

becomes:

=
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(B12,"(",""),")",""),"[",""),"]","")

这个本身就很好用，但是用户也可以手动删除多余的段落：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B12,"(",""),")",""),"[",""),"]","")

Name > substitute()

[美属萨摩亚] > 美属萨摩亚

- Phil

Answer 4

我会采用以下方法：

=SUBSTITUTE(LEFT(A2,LEN(A2)-X),"_","-")

其中X表示您不需要的事物的长度。对于X，我会使用

(ISERROR(FIND("_S",A2,1))*2)+
(ISERROR(FIND("_40K",A2,1))*4)+
(ISERROR(FIND("_60K",A2,1))*4)+
(ISERROR(FIND("_AB",A2,1))*3)+
(ISERROR(FIND("_CD",A2,1))*3)+
(ISERROR(FIND("_EF",A2,1))*3)

上述的ISERROR(FIND("X",.,.))*x如果没有找到X，将返回0，如果找到了将返回X的长度x。因此，技术上来说，您正在从右侧修剪可能匹配的A2。

与其他方法相比，这种方法的优点在于更清楚地表明了正在进行的替换（或删除）操作，因为“替换”不是嵌套的。

- Werner

Answer 5

不再需要使用VBA了！现在有一个超级简单的公式！

=REDUCE(A1,$B$2:$B$6,LAMBDA(a,b,SUBSTITUTE(a,b,OFFSET(b,0,1))))

其中A1是要替换文本的目标单元格 B2:B6是关键词存储的位置（要替换的文本） C2:C6（由偏移引用）是替换文本存储的位置

来源：Chandoo

- Jacob

Answer 6

=SUBSTITUTE(text, old_text, new_text)

if: a=!, b=@, c=#,... x=>, y=?, z=~, " "="     "
then: abcdefghijklmnopqrstuvwxyz ... try this out
equals: !@#$%^&*()-=+[]\{}|;:/<>?~     ...     ;}?     ;*(|     ]:;

规则：

(1) 要替换的文本在A1单元格中
(2) 最多64个替换级别（下面的公式只有27个级别[字母+空格]）
(3) “旧文本”不能是“新文本”（即：如果a=z，则z不能是“旧文本”）

---so if a=z,b=y,...y=b,z=a, then the result is 
---abcdefghijklmnopqrstuvwxyz = zyxwvutsrqponnopqrstuvwxyz (and z changes to a then changes back to z) ... (pattern starts to fail after m=n, n=m... and n becomes n)

公式如下：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"a","!"),"b","@"),"c","#"),"d","$"),"e","%"),"f","^"),"g","&"),"h","*"),"i","("),"j",")"),"k","-"),"l","="),"m","+"),"n","["),"o","]"),"p","\"),"q","{"),"r","}"),"s","|"),"t",";"),"u",":"),"v","/"),"w","<"),"x",">"),"y","?"),"z","~")," ","     ")

- user12380371

---

仅包含代码而没有上下文或解释的答案通常被认为是低质量的。请考虑添加更多信息以改善您的答案。 - brae