从字符串中提取数字

2024 年 11 月 13 日 | 阅读 9 分钟

如何从字符串中提取数字？

默认情况下，Excel 包含各种函数和公式，用于根据要求检索指定或选择的数据。用户输入的数据是文本、数字或两者的组合。有时出于计算目的需要提取或解析数据。如果数据长度较短，用户可以手动检索数据。尽管手动过程有效，但它花费的时间更多，有时会导致错误，这在大数据集是不可能的。为了解决这个问题，Excel 提供了提取文本字符串中的文本或数字的必要公式和函数。有不同的方法可以从文本字符串中提取文本。但是，一堆函数被嵌套在一起以从文本字符串中提取数字。在本教程中，将解释从文本字符串中提取数字的方法。

示例 1：使用公式提取数据开头处的数字。

要提取位于数据开头处的数字，请遵循以下步骤：

步骤 1：在工作表中输入数据，即 A2:A6

步骤 2：选择用户希望显示结果的新单元格，然后输入公式 = SUBSTITUTE(A2, RIGHT(A2, LEN(A2)-MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9}, A2)," "))),"")

步骤 3：按 Enter 键。结果将显示在选定的单元格中。要显示所有单元格的结果，请将公式拖至单元格 B6。

上面的工作表解析了文本字符串中的数字，这些数字显示在单元格 A2:A6 中。

公式解释如下：

这里，来自上述数据，它是文本和字母的组合。因此，各种函数嵌套在一起，例如 MAX、RIGHT 和 LEN。

MAX 在函数中返回最大值

RIGHT 从字符串的右侧提取指定数量的字符

LEN 用于查找给定字符串的长度

上述函数用于查找数字的位置并从字符串中删除文本。

有关详细说明，公式的分解如下：

FIND({0,1,2,3,4,5,6,7,8,9},A2)

FIND 函数查找单元格 A2 中所有数字的位置。除了数字六和七，它会返回 #VALUE! 错误，因为它无法找到其他数字，例如（0、1、2、3、4、5、8、9）。因此，它在单元格 A2 中分别返回 6 和 7 的位置，分别为一和二。

IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),"")

IFERROR 函数的目的是使数组中的所有错误元素变为空白并返回数组公式。

MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2)

MAX 函数查找数组元素中存在的值最大的元素。

LEN(A2)-MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2)

LEN 函数提取或删除单元格中的所有文本字符。因此，首先计算字符串的长度并减去数字值的位置。这里 7-2=5。

RIGHT(A2,LEN(A2)-MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2)

这里 RIGHT 函数用于提取数字右侧的五个字符，即从第 2 个字符开始。

应用 SUBSTITUTE 函数会删除此字符串并将其替换为空白单元格。因此，结果将显示为“APPLE”。最后，将显示数字值 67 作为结果。

公式的工作过程如下所示：

SUBSTITUTE(A2,RIGHT(A2,LEN(A2)-MAX(IFERROR({#VALUE!,#VALUE!,1,#VALUE!,#VALUE!,2,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

),""))),"")
=SUBSTITUTE (A2, RIGHT (A2, LEN (A2)-MAX ({","", 1,","", 2,",","","}<
)),"")
=SUBSTITUTE (A7, RIGHT (A2, LEN (A2)-2),"")
=SUBSTITUTE (A7, RIGHT (A7, 7-2),"")
=SUBSTITUTE (A7,"APPLE","")
=67

示例 2：如何从文本中提取数字，如果它存在于数据中的任何位置？

要提取存在于数据中任何位置的文本，请遵循以下步骤：

步骤 1：在工作表中输入数据，即 A1:A6。

步骤 2：选择用户希望显示结果的新单元格，然后键入公式 =TEXTJOIN(", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:"&LEN(A2))),1)*1,")).

步骤 3：按 Enter 键。嵌套函数提取数据中存在的数字。要显示其余单元格的数据，请将公式拖至单元格 B6。

从上面显示的工作表中，嵌套函数解析文本字符串中的数字，并将结果显示在选定的单元格中。

公式解释如下：

INDIRECT - 此函数返回值范围的引用

ROW - 它返回引用的行号。

MID - 它提取字符串中提到的字符数

TEXTJOIN - 此函数使用分隔符合并来自字符串或范围的文本。

LEN (A2) - 它返回单元格 A2 中存在的字符串长度。

(INDIRECT ("1:"&LEN (A2)))- 它返回从 1 到 12 的行引用

ROW (INDIRECT ("1:"&LEN (A2))), 1) - 此函数返回指定行的行号。因此，ROW 函数返回数组 {1; 2; 3;4;5;6;7;8;9;10;11;12;13}

(MID (A2, ROW (INDIRECT ("1:"&LEN (A2))), 1) - MID 函数用于提取 A2 中的字符并以单独元素的形式返回数组，例如 {"a"; "p"; "p"; "l"; "e"; "s"; "1"; "0"; "0"; "s"; "o"; "l"; "d"}

IFERROR (MID (A2, ROW (INDIRECT ("1:"&LEN (A2))), 1)

IFERROR 函数将所有非数字字符替换为 1。它将所有字符乘以 1。如果元素是非数字的，它将返回一个错误。如果它是数字值，它将返回相同的值，该值乘以一。结果将显示为：

{"";"";"";"";"";""; "1"; "0": "0":"":"":"":""}

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

TEXTJOIN 函数合并存在的剩余元素。这里只存在数字值。因此，它将数字值合并为 100。

公式的工作原理简而言之如下：

=TEXTJOIN (", TRUE, IFERROR (MID (A2, ROW (INDIRECT ("1:"&13)), 1)*1,"))

=TEXTJOIN("",TRUE,IFERROR(MID(A2,{1;2;3;4;5;6;7;8;9;10;11;12;13},1)*1,""))

=TEXTJOIN("",TRUE,IFERROR({"a";"p";"p";"l";"e";"s";"1?;"0?;"0?;"s";"o";"l";"d"}

*1,""))

=TEXTJOIN("",TRUE,IFERROR(MID(A2,{1;2;3;4;5;6;7;8;9;10;11;12;13},1)*1,""))

=TEXTJOIN ("", TRUE, {"";"";"";"";"";""; 1; 0; 0 ;"";"";"";""})

=100

示例 3：如何从数据末尾的文本中提取数字？

要提取位于数据末尾的文本，请遵循以下步骤：

步骤 1：在工作表中输入数据，即 A1:A6。

步骤 2：选择用户希望显示结果的新单元格，然后键入公式 =SUBSTITUTE(A2, LEFT(A2, MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9}, A2),"))-1),"")

步骤 3：按 Enter 键。嵌套函数提取数据末尾的数字。要显示其余单元格的数据，请将公式拖至单元格 B6。

从上面显示的工作表中，嵌套函数解析文本字符串中的数字，并将结果显示在选定的单元格中。公式解释如下：

FIND({0,1,2,3,4,5,6,7,8,9}, A2)- FIND 函数用于查找单元格 A2 中给定数字的位置。它显示结果如下：

{#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,5,6,7, #VALUE!}

除了第 7、8 和第 9 位数字的位置外，它都会返回错误消息。FIND 函数除了 5、6 和 7 之外，无法在单元格 A2 中找到数字。因此，第 7、8 和第 9 位分别返回值为 5、6 和 7。

IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2)

IFERROR 函数的作用是使数组中存在的所有错误元素变为空白单元格，但保留数字值。因此，数组将如下所示：

{"","","","","","","", 5, 6, 7,""}

MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2)

MIN 元素的作用是返回数组中值最小的元素。显示第一个数字值的位置。在此示例中，第一个数字值的位置是 7。

LEFT(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2) ,""))-1),"")

这里 LEFT 函数提取数据中的所有文本字符。它是从后向前提取的，这意味着 MIN 函数返回值为 7，这是数字值的起始位置。值 1 从 7 中减去（7-1=6）。结果将得到“APPLES”。

=SUBSTITUTE(A2,LEFT(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2) ,""))-1),"")

SUBSTITUTE 函数将获得的文本字符串（APPLES）替换为空白。因此，获得的结果是 100。

公式的工作过程如下所示：

=SUBSTITUTE(A2,LEFT(A2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),""))-1),"")

=SUBSTITUTE(A2,LEFT(A2,MIN(IFERROR({{#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,5,6,7,#VALUE!}),""))-1),"")

=SUBSTITUTE (A2, LEFT (A2, MIN ({",",",",","", 5, 6, 7,"}))-1),"")

=SUBSTITUTE (A2,"APPLES","")

=100

示例 4：Flash Fill 方法如何从文本字符串中提取数字？

Flash Fill 方法是从文本字符串中提取数字的最简单方法之一。如果它遵循第一个单元格中存在的模式。应遵循以下步骤：

步骤 1：在工作表中输入数据，即 A1:A6

步骤 2：选择用户希望显示结果的新单元格 B2。在 B2 中键入 A2 数据中的数字值。输入数据后，按 CTRL+E 或单击“数据”>“快速填充”。

步骤 3：右键单击单元格 B2 并拖至 B6。快速填充方法会自动填充数据。

上面显示的工作表使用快速填充方法填充数据。

示例 5：如何从给定数据中提取五位数字？

该公式使用 CONCATENATE 和 SEQUENCE 函数从字符串中获取五位数字文本字符。应遵循以下步骤：

步骤 1：在工作表中输入数据，即 A1:A6

步骤 2：选择用户希望显示结果的新单元格，即 B2。在单元格中键入公式 =CONCAT (IFERROR (0+MID (A2, SEQUENCE (LEN (A2)), 1),")).

步骤 3：按 Enter 键。结果将显示在选定的单元格中。

公式解释如下：

(LEN (A2))- length 函数返回单元格 A2 中存在的字符串长度。它返回输出 8。

SEQUENCE (8) - SEQUENCE 函数返回数据的前八个位置。输出将显示为 {1;2;3;4;5;6;7;8}

MID (A2, SEQUENCE (LEN (A2)), 1),")) - 输出为 {"1"; "6"; "A"; "B"; "C"; "5"; "6"; "1"}。将 0 添加到字符串 0+{"1"; "6"; "A"; "B"; "C"; "5"; "6"; "1"}。结果将显示为 {1; 6; # VALUE!; # VALUE!; # VALUE!;5;6;1}

(IFERROR (0+MID (A2, SEQUENCE (LEN (A2)), 1),""))- IFERROR 函数将错误值替换为空白，因此输出将显示为

{1; 6;"";"";""; 5; 6; 1}

CONCAT (IFERROR (0+MID (A2, SEQUENCE (LEN (A2)), 1),""))

CONCATENATION 函数用于添加输出中的数字。因此，结果将显示为 16561。

示例 6：如何使用 Excel VBA 宏仅从给定数据中提取数字？

Excel VBA 模块有助于从文本字符串中提取数字。它是公式和函数的替代方法之一。该代码要求用户输入用户可以输入相应数据范围的输入和输出范围。

使用 Excel VBA 模块的步骤如下：

步骤 1：按 ALT+F11。将打开一个 VBA 窗口。