☰

如何分隔单元格中的最后 4 个数字或最后 8 个数字（如果带有破折号）？同时提取句子的中间部分？

tag-icon • tag-icon microsoft-excel worksheet-function
如何分隔单元格中的最后 4 个数字或最后 8 个数字（如果带有破折号）？同时提取句子的中间部分？

这是 3 个不同行中的 3 个不同项目。希望结果返回到其旁边的列中。目标是提取年份，例如：

2012-2014

2012-2013

2019

下面是 km 正在使用的数据，每个数据位于不同的行。

Sugar Coops (top) 2012-2014

Sugar Coops (bottom) 2012-2013

Red Apples 2019

对于中间的提取，希望采取：

Coops (top)

coops (bottom)

apples

无法添加图片，抱歉。这些单元格希望能够提取年份和中间部分，例如 coops（顶部）。本质上是想把这个句子分成 3 个部分。第一个单词在一列，另一列是中间部分，等等

谢谢你！

答案1

您的数据看起来“格式良好”：它遵循常规模式，即使不是很明显，并且看起来该模式没有不规则之处。

  1. 您首先需要的是单个单词。例如，名字字段实际上可以包含多个单词，并用空格隔开。因此，后面的空格很容易准确地用作单词结尾的标记。

  2. 您需要从数据中获得的最后一项内容是相同的，只是在您需要的字符串前面有一个空格。这里的技巧是弄清楚 Excel 如何知道它在哪里，以便它可以开始提取该字符串。这很讨厌，但肯定可行。

  3. 您需要的第三件事是……其他一切。这也可以称为“原始字符串，删除前两件事，然后精心修饰。”这也是可行的。

因此，第一部分，即数据中的第一个单词：只需使用该LEFT()函数并告诉它在空格前停止即可。您可以使用 Excel 的FIND()函数了解空格的位置，并从其返回的值中减去 1：

=LEFT( A1, FIND( " ", A1 ) - 1 )      (Put in B1, say.)

（我将使用 A1 作为将数据字符串分开的单元格。Excel 将找到空格，减去 1，然后从字符串左侧取出那么多个字符。全部完成。

第二部分将在第三部分之后讨论。那么如何找到字符串中的最后一个空格呢？有很多种方法，特别是如果您了解 VBA 并被允许使用它。以下是最简单的，真的，公式没有什么奇怪和难以理解的地方，获得结果也不困难，没有命名范围，只是简单和标准函数的简单组合：（ RIGHT()就像我们LEFT()刚才使用的，FIND()我们也使用过，和SUBSTITUTE()。

为什么不只FIND()针对最后一个空格？好吧，FIND()不会寻找第 9 个空格。如果您愿意，它会从字符 2,174 开始查找，但不会寻找第 3 个空格或第 7 个空格。所以你需要告诉它从哪里开始。问题是，没有人FUNCTION()会真正告诉你那是哪里。但是如果你在寻找一个非常独特的角色，那么无论如何，只有一个人会FIND()很高兴地找到它并告诉你从哪里开始获取你真正想要的数据。（用它来告诉RIGHT()~ how many characters to take, you will add 1 to it, but you get the idea.)SUBSTITUTE()`，很乐意替换字符串中的第 3 个或第 4 个或第 232 个“某物”。（字符串，而不是单元格：您甚至可以在根本不存在于任何单元格中的公式中构建字符串，并在它们上使用它！）如何找出哪个是第 x 个？

如果您知道单元格中字符串的长度，并使用类似于SUBSTITUTE()删除所有空格的方法（因为我们想知道要使用哪个空格），那么新长度将减少多少。从原始长度中减去它将告诉您有多少个空格。因此，如果有 251 个，您需要第 251 个空格，并且可以将此结果输入以SUBSTITUTE()告诉它要使用哪个实例。这里有很多东西被替换，但它们都是不同的，所以很容易跟踪。

因此，使用SUBSTITUTE()来放置那个唯一字符，FIND()获取它在字符串中的位置，添加一个以获取所需数据的开头，并RIGHT()实际收集它（使用 的另一个帮助LEN()）。像这样：

=RIGHT( A1, LEN(A1) - FIND( "¢", SUBSTITUTE(A1, " ", "¢", LEN(A1)-LEN( SUBSTITUTE( A1, " ", "" )))))      (Put this in D1.)

现在您已经知道了第一点和第三点。如果 Excel 可以进行“字符串计算”，然后您就可以从原始数据中“减去”这些字符串就好了！您可以直接使用这种方法，然后修饰结果。

如果您SUBSTITUTE()再次使用相同的公式，两次，第一次查找 B1 的内容并用空白替换原始字符串中的空白，第二次对 D1 的内容执行相同操作，您将获得中间内容，这几乎就是您需要的。想必您不希望那些第一个或最后一个空格领先和落后于它，因此要摆脱它们，您有两个选择：不是只删除 B1（和 D1）的内容，而是告诉SUBSTITUTE()删除每个字符串和一个空格。在这里有效，但它不是一个很好的通用解决方案。

Excel 有一个函数，TRIM()可以删除字符串中的首空格、尾空格，还可以“清除”字符串内的任何多余空格。（字符串中不是单独的空格！相反，如果一行中有 2、3、4、2,175 个空格，它会将它们减少为单个空格。）TRIM()将很好地删除剩下的两个空格，因为它们是字符串的开始和结束。缺点是删除字符串内部的空格，如果它们可能发生并且需要保留（如果需要删除它们，那就没问题了，是吗？）。因此，TRIM()如果这不是问题，请选择，或者如果这是一个问题，则将空格添加到 B1 和 D1 内容上以进行删除。我假设它不在以下内容中：

=TRIM( SUBSTITUTE( SUBSTITUTE( A1, B1, "" ), D1, "" ) )      (Put in C1)

注意到内部如何SUBSTITUTE()删除 B1 的内容，然后生成的字符串是外部处理的材料吗？它删除 D1 的材料，然后TRIM()删除前导空格和末尾的空格。

最后，考虑最后一个问题，即那些年份值。您是否希望一些是文本，从单元格的左边缘开始，一些是数字，从单元格的右边缘开始并向左边缘？嗯，这有时是一个问题。但这里不是。当 Excel 从字符串函数（如LEFT()或 ）返回时RIGHT()，它会将结果视为文本。因此，它们都将表现为文本，并且大多数查看它们的其他函数（例如VLOOKUP()）也会将它们视为文本。但有些不会......

因此，如果使用文本值进行搜索，类似这样的查找VLOOKUP()将无法找到“2014”。如果您在执行此操作一年后看到或听到它，则很难弄清楚它失败的原因。如果您使用它的近似功能，情况可能会更加困难，因为在 Excel 的脑海中，“2016-2018”似乎不会遵循简单的“2016”。您可以通过将函数包装TEXT()在 D 列的所有结果中来保护自己，如下所示：

=TEXT( D1, "@" )

（实际上，您应该使用上面针对 D 列的公式，而不是“D1”。）

这使得 Excel 可以将列中的所有内容视为文本，而无需将列格式化为文本（因为... 那么其中的公式将不起作用... 是吗？）。您使用“@”格式，这样文本结果就可以正常显示。

现在 Excel 仍将允许某些函数将 D 列中的值视为数字（如果是的话），但仅限于大多数情况下不会造成问题的函数。例如，查找函数现在可以正常工作（只要您记得将它们的搜索值也设为文本！）。

还有很多其他方法可以做到这一点，有些非常巧妙，很多都使用 VBA，甚至使用 UDF（用户定义函数）。但这非常简单。篇幅只是解释，而不是工作。

相关内容