如何在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_","_"),"_","-")

(请注意，这可能会超过旧的Excel限制，即7个嵌套语句.我正在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屋！