在Excel中使用公式提取数字的技巧

在处理数据时，我们经常需要从文本字符串中提取数字。Excel 提供了多种方法来实现这一需求，无论是简单的数字还是复杂的包含文本和数字的字符串。以下是一些在Excel中提取数字的技巧和方法。

使用公式提取数字

1. 使用 MID 和 MIN 函数组合

当数字位于字符串的开始位置时，可以使用 MID 和 MIN 函数组合来提取数字。

=MID(A1, MIN(IF(ISNUMBER(--MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1)), ROW(INDIRECT("1:" & LEN(A1))), LEN(A1)+1)), LEN(A1))

这个公式的工作原理是： - MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1) 从单元格A1中逐个字符提取。 - --MID(...) 将提取的字符转换为数字，非数字字符将转换为错误。 - IF(ISNUMBER(...), ROW(...), LEN(A1)+1) 返回数字字符的位置，错误则返回一个大于字符串长度的值。 - MIN(...) 找到最小值，即第一个数字字符的位置。 - MID(..., MIN(...), LEN(A1)) 从第一个数字字符开始提取直到字符串末尾。

2. 使用 TEXTJOIN 和 MID 函数组合

当数字和文本混合在一起时，可以使用 TEXTJOIN 函数结合 MID 和 SEARCH 函数来提取数字。

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A1, SEQUENCE(LEN(A1)), 1)), MID(A1, SEQUENCE(LEN(A1)), 1), ""))

这个公式的工作原理是： - SEQUENCE(LEN(A1)) 生成一个从1到字符串长度的序列。 - MID(A1, SEQUENCE(LEN(A1)), 1) 从单元格A1中逐个字符提取。 - IF(...) 判断每个字符是否为数字。 - TEXTJOIN(...) 将所有数字字符连接起来。

3. 使用 VALUE 函数

如果字符串中数字位于开始位置，并且后面紧跟着文本，可以使用 VALUE 函数直接提取数字。

=VALUE(LEFT(A1, MIN(IF(ISNUMBER(--MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1)), ROW(INDIRECT("1:" & LEN(A1))), LEN(A1)+1)))-1)

这个公式的工作原理是： - LEFT(...) 提取从字符串开始到第一个非数字字符之前的所有字符。 - MIN(...) 和 IF(...) 用于确定第一个非数字字符的位置。 - VALUE(...) 将提取的文本转换为数字。

4. 使用 SUBSTITUTE 和 REPT 函数组合

当数字和文本混合在一起时，可以使用 SUBSTITUTE 和 REPT 函数来提取数字。

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$300),1))*ROW($1:$300),0),ROW($1:$300))+1,1)*10^ROW($1:$300)/10)

这个公式的工作原理是： - MID(A1,ROW($1:$300),1) 从单元格A1中逐个字符提取。 - ISNUMBER(--MID(...)) 判断每个字符是否为数字。 - ROW($1:$300) 生成一个从1到300的序列，用于确定数字的位置。 - SUMPRODUCT(...) 计算所有数字字符的总和。

结论

在Excel中提取数字可以通过多种公式实现，具体使用哪种方法取决于数字在字符串中的位置以及字符串的复杂性。上述方法提供了从简单到复杂的场景下提取数字的解决方案。掌握这些技巧将有助于提高数据处理的效率和准确性。

AI办公助手：WPS灵犀

如果本文未能解决您的问题，或者您在办公领域有更多疑问，我们推荐您尝试 WPS灵犀 —— 一款强大的人工智能办公助手。

WPS灵犀 具备AI搜索、读文档、快速创作、生成PPT、长文写作、网页摘要、截图问答、上传文件等功能快来体验吧