如何在 Excel 中从字符串提取数字：找到合适的方法

在 Excel 中进行数据清洗时，您可能会遇到从字母数字混合字符串中提取数字的任务。如果您的数据中数字嵌入在字符串中（例如产品代码或问卷回答），这类问题就很常见。

本文将向您展示使用 RIGHT()、LEN() 和 FIND() 等公式在不同场景下从字符串中提取数字的多种方法。如果您对一些基础还不熟悉，建议您先学习我们的Introduction to Excel课程，打好 Excel 基础。您也可以查看我们的Data Preparation in Excel课程，进一步了解如何转换原始数据。

在 Excel 中从字符串提取数字的公式

Excel 提供了多种基于公式的方法来从字符串中提取数字。针对数字在字符串中的不同位置，每种方法适用的场景也不同。如果您时间紧张，可以直接跳到最能解决您当下需求的部分。

在字符串开头提取数字

有时，数字位于字符串开头。在这种情况下，您可以使用 LEFT() 和 FIND() 来提取数字。将函数组合成一个公式后，会按照以下逻辑提取数字：

• FIND()：查找字符串中第一个字母的位置。

• LEFT()：从字符串开头提取到第一个字母前一位的所有字符。

下面公式的第一部分会找到字符串中的第一个字母；第二部分则提取该字母之前的所有内容，只留下前导数字。如果不存在字母，则返回整个字符串。

=LEFT(A2, FIND(MID(A2, MIN(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}, A2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1), A2) - 1)技术支持

在 Excel 中从字符串开头提取数字。图片来源：作者。

当您处理常见的数字前缀（如订单号、会员 ID、批次号）时，此方法非常适用。

在字符串末尾提取数字

如果您想从字符串末尾提取数字，需要使用 RIGHT()、LEN() 和 FIND() 函数。假设 Excel 表中有如下数据。您可以将这些函数组合起来提取数字，其逻辑为：

• FIND()：找到字符串中第一个数字的位置。

• LEN()：计算从第一个数字到字符串末尾的字符数。

• RIGHT()：从第一个数字开始提取到字符串末尾的所有字符。

下面的公式会移除所有前置文本，并返回字符串中第一个数字及其后面的所有内容。

=RIGHT(A2, LEN(A2) - MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A2&"0123456789")) + 1)技术支持

在 Excel 中从字符串末尾提取数字。图片来源：作者。

该方法最适用于具有一致字母数字结构的数据集，如产品 SKU、订单 ID 和序列号。

在字符串任意位置提取数字

如果您的数据集中数字可能出现在字符串的任意位置，就需要使用 TEXTJOIN()、MID() 和 ROW() 的高级公式来提取数字。

将这些函数组合成一个公式后，会按照以下逻辑从字符串中提取数字： 

• ROW() 和 MID()：遍历字符串中的每个字符，判断其是否为数字。

• IFERROR()：通过逻辑判断将识别出的数字与其他字符区分开。

• TEXTJOIN()：将所有提取出的数字字符合并为一个数字字符串。

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))技术支持

从字符串任意位置提取数字。图片来源：作者。

当您的数据集由字母数字混合且数字可能出现在任意位置时，此方法最为合适。需要注意的是，这些函数在旧版本 Excel 中可能并不全部可用。请查看我们的 Excel Fundamentals 技能路径，了解最新 Excel 版本中的函数与公式。

通过过滤无关字符来提取数字

有时，您的数据会比较杂乱。幸运的是，您可以组合使用 SUBSTITUTE() 和 TEXTJOIN()。只需按照以下步骤：

• SUBSTITUTE()：可以用来将字符串中的所有非数字字符替换为某个分隔符（如空格或空字符串）。例如，您可以将字母和特殊字符替换为空格。

• TEXTJOIN()：随后将剩余字符（此时应只包含数字）连接为一个字符串。

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))技术支持

通过过滤字符从字符串中提取数字。图片来源：作者。

何时需要在 Excel 中从字符串提取数字

下面通过两个小案例来练习如何从字符串中提取数字。

示例 1：从产品代码中提取数字

假设您在管理一份产品清单，每件商品都有唯一的产品代码。这些产品代码是字母数字混合的字符串，如 "PROD1234"、"ITEM5678" 或 "SKU91011"。

因此，要从字母数字混合的产品代码中提取数字型 Product ID，需要根据数字的位置选择函数。记住：若数字在末尾，使用 RIGHT()、LEN() 和 FIND()；若出现在开头，使用 LEFT() 和 FIND()。

下面的示例从代码的右侧提取产品代码中的数字部分。

=RIGHT(A1, LEN(A1) - FIND("-", SUBSTITUTE(A1, MID(A1, MIN(FIND({"0","1","2","3","4","5","6","7","8","9"}, A1&"0123456789")), 1), "-")))技术支持

在 Excel 中从产品代码中提取数字。图片来源：作者。

示例 2：包含嵌入数字的调查数据

调查数据有时会同时包含文本和数字，例如 "Rated 5 out of 10"、"Score: 8" 或 "3 stars"。在计算平均值或识别趋势之前，我们需要先做一些处理。

由于数字可能出现在文本的任意位置，需要使用基于 TEXTJOIN()、MID() 和 ROW() 的动态公式。

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1) * (ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1))), ""))技术支持

在 Excel 中从任意位置提取数字。图片来源：作者。

特殊情形的可复制粘贴代码示例

最后一部分，我为您提供了若干处理不同且更具体场景的公式。您可以直接复制粘贴，希望能解决您遇到的特定问题。 

提取第一段连续数字

如果字符串包含多个数字序列，而您只需要第一组，可使用 LET() 和 SEQUENCE() 的以下方案：

=LET(
txt, A2,
pos, MATCH(TRUE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)), 1)), 0),
len, MATCH(FALSE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)-pos+1)+pos-1, 1)), 0) - 1,
VALUE(MID(txt, pos, len))
)技术支持

在 Excel 中提取第一段连续数字。图片来源：作者。

提取括号内的数字

如果数字出现在括号内，请使用：

=MID(A2, FIND("(", A2)+1, FIND(")", A2)-FIND("(", A2)-1)技术支持

在 Excel 中提取括号内的数字。图片来源：作者。

提取特定字符后的数字

当您的数据使用冒号作为分隔符（例如 "Score: 8"）时，可使用以下公式获取数字：

=TRIM(MID(A2, FIND(":", A2)+1, LEN(A2)))技术支持

在 Excel 中提取特殊字符后的数字。图片来源：作者。

该公式会提取冒号后的所有内容。如果需要数值，可用VALUE() 包裹结果。若分隔符不同，请将冒号替换为相应字符。

提取所有数字并合并为一个值

若要移除所有非数字字符并将每个数字字符合并为一个字符串，请使用：

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)*1, ""))技术支持

在 Excel 中提取所有数字并合并为一个值。图片来源：作者。

提示：在旧版 Excel 中，请将其作为数组公式输入（Ctrl+Shift+Enter）。

提取包含小数点的数字

对于可能包含小数的数字字符串（如 "Price: 45.99"），可使用以下公式同时保留数字和小数点：

=TEXTJOIN("", TRUE, IFERROR(
  IF(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)=".", ".",
     IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)),
        MID(A2, ROW(INDIRECT("1:" & LEN(A1))), 1),
        "")
  ),""))技术支持

在 Excel 中提取包含小数点的数字。图片来源：作者。

注意：该公式假设仅存在一个小数点。

结语

从字符串中提取数字是数据清洗中的一项重要技能。作为数据分析师，我鼓励您掌握并灵活运用本文介绍的不同公式。

如果您希望进一步提升 Excel 技能，建议学习我们的 Data Analysis in Excel 和 Data Visualization in Excel 课程，精通数据分析与展示。也可以尝试我们的 Financial Modeling in Excel 课程，为您打开更多机会之门。