要在Excel中用公式将数字从文本中提取出来，可以使用多种方法，包括使用函数如MID、FIND、LEN和其他文本处理函数。常用方法有：使用MID和FIND函数、使用数组公式、结合SUMPRODUCT函数。其中，使用MID和FIND函数是最为常用和灵活的一种方法。下面将详细介绍如何使用这些方法来提取数字，并结合实际案例进行解释。
一、MID和FIND函数提取数字
使用MID和FIND函数可以灵活地从文本中提取出数字。首先，我们需要知道数字在文本中的位置，然后使用MID函数提取该位置的字符。
1、基本原理
MID函数的基本语法是：MID(text, start_num, num_chars)，其中text是包含数字的文本，start_num是开始提取的位置，num_chars是要提取的字符数量。FIND函数用于查找数字在文本中的位置。
2、具体步骤
确定数字的位置：使用FIND函数找到第一个数字的位置。假设你的文本在单元格A1中，公式为：
=FIND(1,A1&"1234567890")
这个公式会返回第一个数字的位置。如果文本中没有数字，公式会返回一个错误值。
提取数字：使用MID函数从该位置开始提取字符，直到遇到非数字字符为止。可以结合LEN函数来确定文本的长度，从而确定提取的字符数量。
组合公式：将上述步骤组合成一个完整的公式。假设要从A1提取数字，公式为：
=MID(A1, FIND(1, A1&"1234567890"), LEN(A1) - FIND(1, A1&"1234567890") + 1)
二、使用数组公式提取数字
数组公式可以处理复杂的数据提取任务。以下是一个使用数组公式的方法。
1、基本原理
数组公式可以同时处理多个单元格中的数据，并返回一个数组结果。我们可以使用它来逐字符检查文本中的每个字符，判断是否为数字，然后提取出来。
2、具体步骤
创建数组公式：在Excel中输入数组公式需要按Ctrl+Shift+Enter组合键。假设你的文本在A1中，数组公式为：
=TEXTJOIN("", TRUE, IF(ISNUMBER(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1) * 1), MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1), ""))
这个公式会逐字符检查文本中的每个字符，如果是数字则提取出来。
使用辅助列：为了简化数组公式，可以使用辅助列存储每个字符的判断结果，然后合并这些结果。
三、SUMPRODUCT函数提取数字
SUMPRODUCT函数可以用于复杂的数据计算和提取任务。以下是一个使用SUMPRODUCT函数的方法。
1、基本原理
SUMPRODUCT函数通常用于计算数组的乘积和和，但我们可以利用其数组处理能力来提取数字。
2、具体步骤
创建辅助列：在辅助列中逐字符检查文本中的每个字符，判断是否为数字，然后存储这些结果。
使用SUMPRODUCT函数：将辅助列的结果合并成一个数字。假设你的文本在A1中，辅助列在B列，SUMPRODUCT公式为：
=SUMPRODUCT(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1) * (ISNUMBER(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1) * 1)))
四、实际案例应用
案例1：提取混合文本中的数字
假设在单元格A1中有文本"Order1234Shipped"，我们要提取其中的数字1234。
使用MID和FIND函数：公式为
=MID(A1, FIND(1, A1&"1234567890"), LEN(A1) - FIND(1, A1&"1234567890") + 1)
结果为1234。
使用数组公式：输入数组公式
=TEXTJOIN("", TRUE, IF(ISNUMBER(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1) * 1), MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1), ""))
按Ctrl+Shift+Enter，结果为1234。
使用SUMPRODUCT函数：辅助列在B列，SUMPRODUCT公式为
=SUMPRODUCT(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1) * (ISNUMBER(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1) * 1)))
案例2：提取电话号码
假设在单元格A1中有文本"Call me at 555-123-4567"，我们要提取其中的电话号码。
使用MID和FIND函数：公式为
=MID(A1, FIND(5, A1&"1234567890"), LEN(A1) - FIND(5, A1&"1234567890") + 1)
结果为555-123-4567。
使用数组公式：输入数组公式
=TEXTJOIN("", TRUE, IF(ISNUMBER(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1) * 1), MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1), ""))
按Ctrl+Shift+Enter，结果为5551234567。
使用SUMPRODUCT函数：辅助列在B列，SUMPRODUCT公式为
=SUMPRODUCT(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1) * (ISNUMBER(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1) * 1)))
五、注意事项与优化技巧
1、错误处理
在实际应用中，有时文本中可能没有数字，导致公式返回错误值。可以使用IFERROR函数来处理这种情况。例如：
=IFERROR(MID(A1, FIND(1, A1&"1234567890"), LEN(A1) - FIND(1, A1&"1234567890") + 1), "")
如果文本中没有数字，公式将返回空字符串。
2、提高公式效率
对于长文本或大量数据，公式计算可能会比较慢。可以通过以下方法提高公式效率：
减少计算范围：在数组公式中，使用较小的范围而不是整列。
使用辅助列：将复杂公式分解到多个辅助列中，每列执行一部分计算任务，然后合并结果。
3、自定义函数
如果需要频繁进行数字提取操作，可以考虑使用VBA创建自定义函数。以下是一个简单的VBA代码示例：
Function ExtractNumbers(text As String) As String
Dim i As Integer
Dim result As String
For i = 1 To Len(text)
If IsNumeric(Mid(text, i, 1)) Then
result = result & Mid(text, i, 1)
End If
Next i
ExtractNumbers = result
End Function
使用该函数时，只需在单元格中输入公式=ExtractNumbers(A1)即可提取A1中的数字。
六、总结
在Excel中提取数字可以通过多种方法实现，包括使用MID和FIND函数、数组公式以及SUMPRODUCT函数等。其中，使用MID和FIND函数是最为常用和灵活的方法。通过结合LEN函数、IFERROR函数等，可以处理复杂的文本情况，并提高公式的效率。如果需要频繁操作，还可以考虑使用VBA创建自定义函数。希望本文的详细介绍能够帮助你更好地掌握在Excel中提取数字的方法和技巧。
相关问答FAQs：
1. 如何使用Excel公式提取数字？使用Excel公式提取数字非常简单。可以使用以下公式之一来提取数字：
如果数字位于字符串的开头，可以使用LEFT函数。例如，如果A1单元格中的文本为“123ABC”，则可以使用=LEFT(A1,LEN(A1)-LEN(SUBSTITUTE(A1," ","")))来提取数字。
如果数字位于字符串的结尾，可以使用RIGHT函数。例如，如果A1单元格中的文本为“ABC123”，则可以使用=RIGHT(A1,LEN(A1)-LEN(SUBSTITUTE(A1," ","")))来提取数字。
如果数字位于字符串的中间，可以使用MID函数。例如，如果A1单元格中的文本为“ABC123DEF”，则可以使用=MID(A1,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),""))-1,MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),""))-MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),""))+1)来提取数字。
2. 如何将Excel公式提取的数字转换为数值格式？如果使用Excel公式提取数字后，结果仍然是文本格式，可以按照以下步骤将其转换为数值格式：
选中提取出数字的单元格。
在Excel顶部的“开始”选项卡中，找到“数字”组。
在该组中，选择“一般”或“数值”格式。
确保将数字格式应用于所选单元格，以将其转换为数值格式。
3. 如何处理包含多个数字的字符串并提取其中的所有数字？如果字符串中包含多个数字，并且您希望提取所有数字，可以使用以下方法：
使用Excel的文本函数（如MID、LEFT和RIGHT）结合搜索函数（如FIND和SEARCH）来提取每个数字。
使用正则表达式函数（如REGEXEXTRACT）进行高级字符串匹配和提取数字。
使用Excel的宏（VBA）编程来自定义提取数字的逻辑，以便应对更复杂的字符串模式。
原创文章，作者：Edit2，如若转载，请注明出处：https://docs.pingcode.com/baike/4404883