如何在最后一个下划线之后在Excel中提取字符串的最后一部分 [英] How to extract the last part of the string in Excel after the last underscore
问题描述
我有以下示例数据:
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中提取字符串的最后一部分的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!