极客笔记Excel 从字符串中提取数字
如何从字符串中提取数字
默认情况下，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)。因此，它分别将6和7的位置返回到单元格A2的位置一和位置二。
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”； “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函数无法在A2单元格中找到除5、6和7之外的数字。因此，第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，即数值的起始位置。从7减去1（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： 闪现填充方法如何从文本字符串中提取数字？
闪现填充方法是从文本字符串中提取数字的最简单方法之一。如果它遵循第一个单元格中的模式。要按照以下步骤操作，
步骤1：在工作表中输入数据，即A1：A6
步骤2：选择用户希望显示结果的新单元格，即B2。在B2中键入数据，该数据在A2中。输入数据后，按CTRL+E或单击数据>闪现填充。
步骤3：右键单击单元格B2并向B6拖动。闪现填充方法会自动填充数据。
以上工作表使用闪光填充方法填充数据。
示例5： 如何从给定的数据中提取一个五位数？
该公式使用连接和序列函数从字符串中获取五位文本字符。需要按照以下步骤进行操作：
步骤1：在工作表中输入数据，即A1：A6
步骤2：选择一个新单元格，该单元格将显示结果，即B2。在该单元格中输入公式=CONCAT（IFERROR（0+MID（A2，SEQUENCE（LEN（A2）），1），“））。
步骤3：按Enter键。结果将显示在选定的单元格中。
公式解释如下：
(LEN(A2)) – length函数返回单元格A2中字符串的长度。输出结果为8。
SEQUENCE(8) – SEQUENCE函数返回数据的前8个位置。输出将显示为{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窗口。
步骤2：点击插入>模块命令。将显示一个新的模块窗口。
第三步：在模块窗口中输入代码。代码如下所示，
第四步：按下F5执行代码。“输入选择框”将会出现。
步骤5：将指定行输入为A2:A6。
步骤6：将显示一个输出单元选择框。在其中输入指定的输出单元。
步骤7：输入数据后，单击输出单元格范围B2:B6并按Enter键。数据中的文本数字将被解析并显示在输出列B2:B6中。
上面的工作表显示了在单元格B2:B6中从给定数据中解析出的数字。
示例7： 使用分隔符函数提取给定数据中的数字
从给定文本中解析数字的一种最简单的替代函数是使用分隔符。应用分隔符函数的步骤如下：
步骤1：在工作表中输入数据，即A1:A6。在发生文本和数字之间的行中输入数据，如下所示：
步骤2：从A1:A6选择数据。点击“数据>文本分列”选项。
第三步：打开“将文本转换为列向导”。在其中选择 分隔符 选项。点击下一步。
第四步：打开转换文本到列向导对话框2。点击其他选项，并在框中输入“-”连字符符号。点击下一步。数据预览在对话框中显示。点击完成。
第五步：结果将显示在B2:B6单元格中，其中数字是从给定数据中提取出来并显示在所选单元格中。
使用分隔符函数从给定数据中提取指定数据，这是一个更快更简单的过程。
总结
使用包括函数、公式、分隔符和Excel VBA方法的方法来从文本中提取数字。这些函数和公式可以有效地从文本中提取数字。本教程中描述了从文本中提取数字的各种方法。这些方法和函数在本教程中有清晰的解释。