☰

如何在 Excel 中选择单元格中的第一个数字？

tag-icon • tag-icon microsoft-excel microsoft-excel-2013 string-manipulation
如何在 Excel 中选择单元格中的第一个数字？

我有一张表格，其中单元格中存储着数字和一些文本，我想根据数字进行算术运算。所以我的表格如下所示：

Number1       | Number2       | Product
2             | 3             | 6
3 (some text) | 40            | 120
4             | 5 (here too)  | 20

因此，在存在这些丑陋的文本的情况下，我仍然希望能够自动生成第 3 列。我想我需要一个函数来将子字符串的第一个非数字字符解析为数字。我可以用 Excel 做到这一点吗？

附言：数字可以有超过 1 位数字。可以安全地假设它们是整数，但如果能看到适用于浮点数的解决方案就好了 :)

谢谢，

答案1

要获取位于字符串开头的数字，我们可以使用以下命令：

=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))),1))

作为我们的基本公式，这将找到数字的末尾并将其返回作为 MID() 函数的末尾。

这里发生了很多事情：

SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))当这部分遍历数字时，它会用 替换每个数字的最后一个实例}}}。

SUBSTITUTE 的第三个标准是实例。我们找到带有 的实例数LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},""))。它遍历数字，并一次用空值替换每个数字。然后，它找到原始字符串和新字符串的长度差。

因此，在 A2 的情况下，当迭代到时，2它会找到 2，并且外部替代项会用 替换最后一个}}}。这只是一个临时占位符。

Aggregate 函数是一个多功能函数。14 告诉函数我们正在使用该Large()函数。6告诉函数忽略错误。这很重要，因为许多迭代不会找到任何东西并返回错误。

最后1，它告诉函数我们想要从搜索函数中获得最高的返回值，该搜索函数}}}通过迭代搜索每个数字的最后一个实例上放置的临时值。

因此，Aggregate 返回找到的最大数字。我们将其传递给 Mid 函数中的长度标准。

所以现在我们已经找到了字符串前面的数字。

因此我们可以将其中两个相乘以获得所需的输出（任何数学函数都会将返回的字符串转换为数字）：

=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))),1))*MID(B2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"")))),1))

在此处输入图片描述

一个警告Aggregate 函数是在 Excel 2010 中引入的。它可能不适用于旧版本。

如果您使用的是旧版本，则需要使用这个更长的公式：

=MID(A2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},""))))),SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))))))*MID(B2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},""))))),SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"")))))))

它的作用与上面的大致相同，接受它必须先测试错误，然后才能找到最大值。

答案2

假设数字与任何文本之间始终以空格分隔，则可以使用类似这样的方法。假设您的示例数据从 A2 开始，因此 C2 中的第一个结果通常如下所示：

=A2*B2

我们需要用一个公式替换简单的单元格引用，如果源单元格只是数字，则将其视为数字，但如果其中还有文本，则提取数字。因此 C2 将变成：

=IF(ISNUMBER(A2),A2,LEFT(A2,FIND(" ",A2)-1)) * IF(ISNUMBER(B2),B2,LEFT(B2,FIND(" ",B2)-1))

如果源单元格不是数字，它会找到第一个空格并返回其前面的字符。从技术上讲，它返回的是包含数字的文本值，但 Excel 足够聪明，可以在计算乘积时将其视为数字（无需将其嵌套在另一个函数中以将其转换为数字）。请注意，如果数字是浮点数，这仍然有效。

注意事项：这依赖于“干净的”源数据，即数字条目或以数字开头后跟空格的文本条目。如果某个单元格不符合要求，则需要清理数据或进行某种形式的错误检查。最好的解决方案是拆分两个数据列，以便任何文本都位于单独的单元格中。

相关内容