如何在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 作为输出.

would return animated and customaffin as output.

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

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中最后一个下划线之后提取字符串的最后一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！