问题描述
我正在尝试设置一个函数来重新格式化稍后将被连接的字符串.示例字符串如下所示:
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 函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！