显示来自另一个单元格的字符串，截断所有单词片段

Question 1

关键不是试图理解末尾的片段。您只需要在前 20 个字符中找到最后一个空格字符并将其截断即可。一种常见的方法是使用 SUBSTITUTE 函数。我将分步构建公式来解释其工作原理。

假设单元格 A1 包含该短语。

LEN(SUBSTITUTE(LEFT(A1,21)," ",""))删除前 21 个字符中的所有空格并确定剩余的字符数。我们使用 21 而不是 20 来应对最后一个完整单词在第 20 个字符处结束的情况，因此下一个空格将位于第 21 个字符处。
21-LEN(SUBSTITUTE(LEFT(A1,21)," ",""))：从一开始的 21 个字符中减去这个数字，就可以得到空格的数量。
SUBSTITUTE 允许您使用可选的最后一个参数替换值的特定实例。我们可以使用它来将前 21 个字符中的最后一个空格替换为表达式中不存在的某个唯一字符（我将使用 CHAR(7)）。前面的计算告诉您该数字实例是什么：
```
SUBSTITUTE(A1," ",CHAR(7),21-LEN(SUBSTITUTE(LEFT(A1,21)," ","")))
```
现在我们有一个唯一的目标，要在前 21 个字符中的最后一个空格的位置寻找。
```
FIND(CHAR(7),SUBSTITUTE(A1," ",CHAR(7),21-LEN(SUBSTITUTE(LEFT(A1,21)," ",""))))
```
没有任何剩余单词片段的表达式在该位置前一个字符结束。因此我们可以使用它来知道要从原始表达式中取出多少个字符。 结果是：
```
=LEFT(A1,FIND(CHAR(7),SUBSTITUTE(A1," ",CHAR(7),21-LEN(SUBSTITUTE(LEFT(A1,21)," ",""))))-1)
```

您可以看到，当 20 个字符位于“saves”中间时（如您的示例所示），它会在前一个单词处结束。当我添加“big”时，20 个字符会以完整单词“time”结束。

警告：请注意，在某些特殊情况下，公式原样会失效。

如果表达式正好有 20 个字符，并且最后一个字符完成一个单词，则会产生错误，因为实际上没有 21 个字符。如果您的文本可以包含简短的表达式，则可以通过检查是否少于 21 个字符来处理，在这种情况下，它会为您提供整个字符串。它可能看起来像：
```
=IF(LEN(A1)<21,A1,LEFT(A1,FIND(CHAR(7),SUBSTITUTE(A1," ",CHAR(7),21-LEN(SUBSTITUTE(LEFT(A1,21)," ",""))))-1))
```
或者，如果至少有 21 个字符，而第 21 个字符是标点符号，那么它就不够智能，无法区分标点符号并识别出实际单词在第 20 个字符处结束。根据它需要处理的情况范围，您可能需要将其变成一个小型应用程序。例如，它需要识别和处理的标点符号集合是什么？带连字符的单词呢，其中连字符是第 21 个字符？格式化符号呢，例如不间断空格、特殊宽度的空格、制表符等？对于 URL 或电子邮件地址，句点或实际上@是单词的一部分，该怎么办？您会发现这很快就会变得非常复杂。

Answer

关键不是试图理解末尾的片段。您只需要在前 20 个字符中找到最后一个空格字符并将其截断即可。一种常见的方法是使用 SUBSTITUTE 函数。我将分步构建公式来解释其工作原理。

假设单元格 A1 包含该短语。

LEN(SUBSTITUTE(LEFT(A1,21)," ",""))删除前 21 个字符中的所有空格并确定剩余的字符数。我们使用 21 而不是 20 来应对最后一个完整单词在第 20 个字符处结束的情况，因此下一个空格将位于第 21 个字符处。
21-LEN(SUBSTITUTE(LEFT(A1,21)," ",""))：从一开始的 21 个字符中减去这个数字，就可以得到空格的数量。
SUBSTITUTE 允许您使用可选的最后一个参数替换值的特定实例。我们可以使用它来将前 21 个字符中的最后一个空格替换为表达式中不存在的某个唯一字符（我将使用 CHAR(7)）。前面的计算告诉您该数字实例是什么：
```
SUBSTITUTE(A1," ",CHAR(7),21-LEN(SUBSTITUTE(LEFT(A1,21)," ","")))
```
现在我们有一个唯一的目标，要在前 21 个字符中的最后一个空格的位置寻找。
```
FIND(CHAR(7),SUBSTITUTE(A1," ",CHAR(7),21-LEN(SUBSTITUTE(LEFT(A1,21)," ",""))))
```
没有任何剩余单词片段的表达式在该位置前一个字符结束。因此我们可以使用它来知道要从原始表达式中取出多少个字符。 结果是：
```
=LEFT(A1,FIND(CHAR(7),SUBSTITUTE(A1," ",CHAR(7),21-LEN(SUBSTITUTE(LEFT(A1,21)," ",""))))-1)
```

您可以看到，当 20 个字符位于“saves”中间时（如您的示例所示），它会在前一个单词处结束。当我添加“big”时，20 个字符会以完整单词“time”结束。

警告：请注意，在某些特殊情况下，公式原样会失效。

如果表达式正好有 20 个字符，并且最后一个字符完成一个单词，则会产生错误，因为实际上没有 21 个字符。如果您的文本可以包含简短的表达式，则可以通过检查是否少于 21 个字符来处理，在这种情况下，它会为您提供整个字符串。它可能看起来像：
```
=IF(LEN(A1)<21,A1,LEFT(A1,FIND(CHAR(7),SUBSTITUTE(A1," ",CHAR(7),21-LEN(SUBSTITUTE(LEFT(A1,21)," ",""))))-1))
```
或者，如果至少有 21 个字符，而第 21 个字符是标点符号，那么它就不够智能，无法区分标点符号并识别出实际单词在第 20 个字符处结束。根据它需要处理的情况范围，您可能需要将其变成一个小型应用程序。例如，它需要识别和处理的标点符号集合是什么？带连字符的单词呢，其中连字符是第 21 个字符？格式化符号呢，例如不间断空格、特殊宽度的空格、制表符等？对于 URL 或电子邮件地址，句点或实际上@是单词的一部分，该怎么办？您会发现这很快就会变得非常复杂。

Question 2

我想这就是你要找的：

英语（未测试）：

=LEFT(A1,21-SEARCH(" ",SUBSTITUTE(RIGHT(A1,21)," "," ",21-LEN(SUBSTITUTE(RIGHT(A1,21)," ","")))))

法语（已测试）：

=GAUCHE(A1;21-CHERCHE(" ";SUBSTITUE(DROITE(A1;21);" ";" ";21-NBCAR(SUBSTITUE(DROITE(A1;21);" ";"")))))

改编自：https://forum.excel-pratique.com/viewtopic.php?t=3120

Answer

我想这就是你要找的：

英语（未测试）：

=LEFT(A1,21-SEARCH(" ",SUBSTITUTE(RIGHT(A1,21)," "," ",21-LEN(SUBSTITUTE(RIGHT(A1,21)," ","")))))

法语（已测试）：

=GAUCHE(A1;21-CHERCHE(" ";SUBSTITUE(DROITE(A1;21);" ";" ";21-NBCAR(SUBSTITUE(DROITE(A1;21);" ";"")))))

改编自：https://forum.excel-pratique.com/viewtopic.php?t=3120

Question 3

实际上，使用公式来检查最后一个“单词”（字母串）是否为完整单词，是无能为力的。即使你加载了一个庞大的字典来进行拼写检查（公式方法将以 SEARCH() 或 VLOOKUP() 等函数可以搜索的方式加载它），也可能很容易出现其中未包含的有效单词。

可以检查最后一个“单词”中是否有数字或标点符号，但这样就会出现“即使我说的是“单词”，但是什么让它实际上不是一个单词？”的问题。例如，电子邮件地址是一个有效的单词吗？只有完整才算有效吗？标签单词或短语呢？由于标签单词可能是“#ijyrrbt8”，如何知道它是完整的或检查其拼写？

好吧，撇开这一点不谈，我的方法是先检查 21 个（不是 20 个）字符的字符串的最后一个字符。如果它是一个空格，或者是该语言的任何有效标点符号，那么我会认为它前面的任何东西都是完整的，因此是一个有效的单词。使用 IF() 测试对简单的 OR() 子句执行此操作。OR() 子句很简单，但可能很长，这取决于语言在有效单词后可能继续的所有方式，尤其是标点符号。遗憾的是，标点符号也出现在单词内部，例如，表示发生了缩略词：“don't”就是英语中的一个例子。

确定之后，您要么已经完成（假设后面有一个空格，我会认为前面的“单词”是有效单词……除非在末尾注明……），要么尚未完成。如果尚未完成，则继续通过删除其中有问题的结束字符串来减少 20 个字符的字符串。这里使用了一个“技巧”来使 FIND() 工作（它需要您要查找的内容的实例编号，并且遗憾的是不会采用“last”之类的东西）。使用 SUBSTITUTE() 消除单元格或字符串中的所有空格（在本例中为字符串），取结果的 LEN()（从原始结果的 LEN() 中减去它，但您知道那是 20，所以……好吧，除非您不知道（参见示例公式之后的内容）所以取字符串的 LEN()）。结果是字符串中存在多少个搜索到的字符。假设搜索空格时的计算结果是 4。因此，20 个字符的字符串中有四个“空格”，您可以使用 find 来找到最后一个：

=FIND( " ", 4 )

警告：由于字符串本身可能包含空格作为字符 20，因此使用 TRIM() 包装 LEFT()：TRIM(LEFT("20 个字符的字符串正在检查")) 以删除最后一个空格。你为 TRIM() 包装的字符串使用了 LEN()，这是件好事，不是吗？

好的，现在您的值比需要的值高 1，因此从中减去 1，并使用结果作为 LEFT() 函数中指定的长度（而不是 20）：

=TRIM( LEFT( "source string", "length found above" ))

第二个警告：以“Mr.”结尾的字符串是否满足要求？或者，经过深思熟虑，人们是否也会喜欢删除这些东西？如果不是，那就太棒了，容易多了。如果是这样，这将是一个完全有效的思考方式，如果人们认为这样的字符串总是需要后面跟着名字，因此它不是一个有效的“单词”，因为它缺少“单词”这个稍微更宽泛的定义的一部分——人们经常发现，即使是像“单词”这样简单的东西也会偏离主题。我甚至不会尝试演示任何东西来处理这里可能发生的所有怪异现象，只是提出，在找到这样的字符串后，可以让另一个搜索子句从之前的搜索结果中减去 1，从而找到倒数第二个空格而不是最后一个空格……等等。

然后将其应用于相当大的数据集，并寻找由于您没有想到源数据中存在的罕见或不频繁的事情而导致的失败......

抱歉，我似乎有些愤世嫉俗，但我们都知道这种野兽的本质。这绝对是活到老学到老！继续添加 OR() 子句并进行其他调整，以帮助清理实际最终测试的数据。

Answer