如何在最后一个下划线之后在Excel中提取字符串的最后一部分

问题描述：

我有以下示例数据:

1. animated_brand_300x250
2. animated_brand_300x600
3. customaffin_greenliving_solarhome_anim_outage_offer

如何从Microsoft Excel的最后一个下划线中提取字符串?

How to extract the string from the last underscore in Microsoft Excel?

我想提取第一个下划线之前和最后一个下划线之后的值.

I want to extract the value before the first underscore and after the last underscore.

第一个下划线:

=LEFT(B6,SEARCH("_",B6)-1)

将返回animated和customaffin作为输出.

如何在最后一个下划线后返回字符串?

How to return the string after the last underscore?

答

您可以使用RIGHT函数在最后一个_之后找到字符串.棘手的部分是找到最后一个_的位置.

You can find the string after the last _ using the RIGHT function. The tricky part is to find the position of the last _.

首先，我们需要确定有多少_个.这可以通过删除所有_并比较两个字符串的长度来完成:

First, we need to determine, how many _ there are. This can be done be removing all _ and compare the length of both strings:

LEN(A1)-LEN(SUBSTITUTE(A1;"_";""))

由于我们现在知道要查找的是_的第二次出现，因此可以使用SUBSTITUTE函数用另一个字符替换_的第二次出现(该字符必须是不是您原始字符串的一部分-我选择了#).

Since we now know that it is the second occurrence of _ that we have to look for, we can use the SUBSTITUTE function to replace the second occurrence of _ with another character (this has to be a character that is not part of your original string - I have chosen#).

现在，我们使用FIND函数搜索#字符的位置.现在可以将该职位传递给RIGHT函数.

Now, we use the FIND function to search for the position of the # character. This position can now be delivered to the RIGHT function.

您的最终公式将是:

=RIGHT(A1;LEN(A1)-FIND("#";SUBSTITUTE(A1;"_";"#";LEN(A1)-LEN(SUBSTITUTE(A1;"_";"")));1))

如何在最后一个下划线之后在Excel中提取字符串的最后一部分

相关推荐