☰

这是如何运作的？

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
这是如何运作的？

我有以下数据：

http://123.456.789.10.com
http://789.12340.com
http://456.789.1123123rs.com
http://12321.com

我想要做的是获取倒数第二个句点的文本。例如，公式应返回以下值：

10.com
12340.com
1123123rs.com
12321.com

注意：如果只有一个句点（如最后一个例子），我想要删除直到（包括）的所有内容://。

我试过：（值存储在 D2 单元格中）

=RIGHT(D2,FIND(".",D2,FIND(".",D2)+1)-1)（使用“右”时效果不佳，但使用“左”时效果很好）

=RIGHT(D2,FIND(".",D2)+1)

我不知道我错过了什么......

有人可以帮忙吗？

答案1

确信以下公式可以满足您的要求：

算法简要总结：

使用公式评估工具将其分解，并详细了解其工作原理。如评论中所述，您将看到99对返回的字符数设置限制。如果返回的数据可能更长，则可能需要更改此限制

  • 删除所有内容，包括://
  • 将倒数第二个点替换为一个不常用的字符
  • 使用该FIND函数定位该替换，以便为该MID函数生成起始编号
=MID(A1,FIND("://",A1)+2+FIND(CHAR(1),SUBSTITUTE("." &MID(A1,FIND("://",A1)+3,99),".",CHAR(1),LEN("." &A1)-LEN(SUBSTITUTE("." &A1,".",""))-1)),99)

在此处输入图片描述

答案2

=RIGHT(D2, 
 LEN(D2) - FIND("\", SUBSTITUTE(D2, ".", "\", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1))

说明：

首先，我们得到期数使用这个简单的技巧：

  1. 我们确定全文长度：
    =LEN(D2) （见图E中柱子）
  2. 我们确定相同文本的长度，但删除所有句点：
    =LEN(SUBSTITUTE(D2, ".", "")) （见图F中柱子）
  3. 这不同之处将是期数：
    =LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) （见图G中柱子）

其次，我们知道期数，我们能够确定 倒数第二句的发生次数。例如，如果期数为5，则倒数第二期的发生次数为4：

  • =LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1 （见图H中柱子）

第三，我们用它作为第四个参数SUBSTITUTE() 函数 用符号替换出现的句点\：

  • =SUBSTITUTE(D2, ".", "\", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1) （见图I中柱子）

\第四，我们使用 FIND() 函数确定该符号的位置（ ）：

  • =FIND("\", SUBSTITUTE(D2, ".", "\", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) （见图J中柱子）

第五，众所周知该符号的位置（\）和全文的长度， 我们可以确定剩余符号数，即符号后 \例如全文长度为5，位置为\3，则剩余符号为2个：

  • =LEN(D2) - FIND("\", SUBSTITUTE(D2, ".", "\", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) （见图K中柱子）

最后我们将其作为RIGHT()函数的第二个参数：

  • =RIGHT(D2, LEN(D2) - FIND("\", SUBSTITUTE(D2, ".", "\", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) （见图L中柱子）

和这是最终的公式。

在此处输入图片描述

笔记：

问题中的最后一个例子是没有第二个，只有一个句号（与问题标题相矛盾）。为了包括这种可能性，请.在后面添加，即用公式中的http://所有内容替换D2

=SUBSTITUTE(D2, "//", "//.")

答案3

=IF(LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2)), SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", "."), IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1))

这是如何运作的？

首先，我们有一个IF语句，如果有一个（或更少）个句号，或者至少有 2 个句号，我们可以以不同的方式处理事情。

我们的逻辑测试是检查是否有第二个句点：如果在字符串中找不到 2 个句点，则返回此结果；如果有 2 个或更多句点，则LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2))
返回此结果。01

然后，我们首先考虑“如果有 2 个或更多个周期”的公式，然后考虑“如果有少于 2 个周期”的公式，但由于后者更简单，我将首先介绍它。

如果句点少于 2 个：

如果有一个或零个句点，那么我们需要删除直到://开头的文本（包括开头的），因此我们将找到的位置://并仅取其后的文本：RIGHT(A1, LEN(A1)-FIND("://", A1)-2)
以防万一有数据不是以协议结尾的，://我们应该用将其包装起来IFERROR并获取整个原始字符串而不是错误#VALUE，最终结果是：IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1)

如果有（至少）2 个句点

现在我们已经解决了一些简单的情况，让我们看看当有 2 个（或更多）周期时会发生什么：SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", ".")

让我们详细分析一下：

  1. 我们用大量空格替换所有句点（与整个原始字符串中的字符数一样多）：SUBSTITUTE(A1, ".", REPT(" ", LEN(A1)))
  2. 我们只取其末尾部分，具体来说是原始字符串长度的两倍。这给了我们最后两部分，以及两部分之前和之间的一堆空格：RIGHT(<step 1>, LEN(A1)*2)
    注意：如果您还想包含下一节，则应将其更改*2为*3。
  3. 我们使用 删除所有多余的空格TRIM，只留下一个空格，其中剩下的一个句点应该是：TRIM(<step 2>)
  4. 我们将剩余的空格替换为.：SUBSTITUTE(<step 3>, " ", ".")

所以我们的整个公式是： =IF(LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2)), SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", "."), IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1))

答案4

通常的公式用于SUBSTITUTE()将所有感兴趣的字符（在本例中为句点）替换为与可以在单元格中合理找到的数据相比任意长的字符串。

例如，如果句点之间的最长一组字符在您预期的数据中是 95，那么用 250 个“空格”替换每个句点就可以很好地发挥作用。

然后将SUBSTITUTE()函数包装在一个RIGHT()函数中。如果您的结果只有 3 个字符长，则右侧需要 253 个字符。在拾取任何不需要的字符之前，您有 502 个字符。如果结果是预期的 95 个字符加上 250 个空格加上另外 95 个字符，则需要 420 个字符来覆盖自己。但仍然有 502 个字符可以取，而无需取不需要的字符。因此，在您期望的最大值 420 和您可以使用的最大值 502（可能是 490）之间选择一个数字，然后使用函数取最右边的 490 个字符RIGHT()。

现在将SUBSTITUTE()函数包装在该函数周围，这次用“”替换空格，这样引入的所有空格都会消失，并且您就会得到所需的结果。

如果希望得到的结果中可以包含空格，请使用其他不会包含空格的字符。字体的可能字符列表中可能存在一些奇怪的字符。也许是 ¬ (Alt-01452)。或者不要冒险，而是使用它，而不是使用空格。

也不要输入 250 个空格。使用REPT()第一个（内部）SUBSTITUTE()函数中的函数输入一次所选字符，但会得到 250 个！

还有其他方法，但它们通常需要知道有多少个字符。例如，您的数据可能格式正确，足以知道总是有 4 个句点。那么这些方法就很好用了。但如果它可以变化，这里 3 个，那里 7 个，那么……

以上内容是“残酷的”而不是“优雅的”，但谁真的在乎呢？概念简单，使用方便，而且能达到你的目的。

相关内容