如何在 Excel 中组合多个嵌套的 Substitute 函数? [英] How can I combine multiple nested Substitute functions in Excel?

问题描述

我正在尝试设置一个函数来重新格式化稍后将被连接的字符串.示例字符串如下所示:

I am trying to set up a function to reformat a string that will later be concatenated. An example string would look like this:

Standard_H2_W1_Launch_123x456_S_40K_AB

虽然有时S"不存在，有时40K"是60K"或不存在，_AB"也可以是_CD"或_EF"".最后，所有下划线都需要改为连字符.最终产品应如下所示:

Though sometimes the "S" doesn't exist, and sometimes the "40K" is "60K" or not there, and the "_AB" can also be "_CD" or _"EF". Finally, all underscores need to be changed to hyphens. The final product should look like this:

Standard-H2-W1-Launch-123x456-

我有四个函数，如果一个接一个运行，将处理所有这些:

I have four functions that if ran one after the other will take care of all of this:

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

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

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

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

我尝试了多种方法将这些组合成一个函数，但我对这种级别的 excel 还比较陌生，所以我不知所措.有没有办法将所有这些结合起来，以便它在一个单元格中一个接一个地执行一个命令?

I've tried a number of ways of combining these into one function, but I'm relatively new to this level of excel so I'm at a loss. Is there anyway to combine all of this so that it executes one command after the other in one cell?

推荐答案

要简单地组合它们，您可以像这样将它们放在一起:

To simply combine them you can place them all together like this:

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

(请注意，这可能会超过 7 个嵌套语句的旧 Excel 限制.我正在 Excel 2010 中进行测试

(note that this may pass the older Excel limit of 7 nested statements. I'm testing in Excel 2010

另一种方法是使用Left 和Right 函数.

Another way to do it is by utilizing Left and Right functions.

这里假设末尾的变化数据始终存在并且长度为 8 个字符

This assumes that the changing data on the end is always present and is 8 characters long

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

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

This will achieve the same resulting string

如果字符串并不总是以您想要去除的 8 个字符结尾，您可以搜索_S"并获取当前位置.试试这个:

If the string doesn't always end with 8 characters that you want to strip off you can search for the "_S" and get the current location. Try this:

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

这篇关于如何在 Excel 中组合多个嵌套的 Substitute 函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！