excel提取数字的方法

1、如果需要提取数字的源数据没有字母出现,只是汉字和数字,可以使用这个公式提取数字:

=MIDB(A2,SEARCHB("?",A2),2*LEN(A2)-LENB(A2))

2、如果需要提取数字的源数据没有字母出现,只是汉字和数字,可以使用这个公式提取数字:

=-LOOKUP(1,-MID(A2,MIN(FIND(ROW($1:$10)-1,A2&1/17)),ROW($1:$15)))





<think>我们面对的问题:在Excel提取单元格中的数字。由于单元格内容可能包含文本和数字混合,需要提取其中的数字部分。常用的方法包括使用公式(如MID、LEFT、RIGHT等结合FIND或SEARCH)或较新的TEXTJOIN、CONCAT等函数辅助数组公式,或者使用VBA自定义函数。但注意,Excel没有内置的直接提取所有数字的函数,因此需要构建公式。经过搜索,常见的解决方案有以下几种:方法1:使用数组公式(适用于旧版Excel,需按Ctrl+Shift+Enter)公式思路:遍历单元格中的每个字符,判断是否为数字,然后将数字连接起来。公式示例(假设数据在A1单元格):=TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)), MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))说明:该公式在Excel2016及以上版本支持TEXTJOIN函数。ROW(INDIRECT("1:"&LEN(A1)))用于生成1到文本长度的序列,然后MID分别取出每个字符,判断是否为数字(通过--转换为数字,若转换出错则不是数字),再用TEXTJOIN连接。方法2:对于没有TEXTJOIN的版本,可以使用数组公式结合CONCATENATE(但较复杂)或者使用VBA自定义函数。方法3:使用SUBSTITUTE函数逐个替换非数字字符(但需要知道所有非数字字符,不通用)。方法4:使用VBA自定义函数(UserDefinedFunction),可以编写一个函数遍历字符串提取数字。考虑到用户可能没有启用宏,这里重点提供公式方法。另外,还有一种情况:如果数字在字符串中的位置固定(例如总是在开头、结尾或中间特定位置),则可以使用LEFT、RIGHT、MID等函数结合FIND来提取。但是,如果数字位置不固定,则通用方法就是遍历每个字符。下面提供两种公式方法方法一(适用于Excel2016及以上):使用TEXTJOIN函数的数组公式(按Ctrl+Shift+Enter输入,但在Excel365中可能自动支持数组公式):=TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1), ""))方法二(适用于所有版本,但更复杂):使用CONCAT和IF数组(同样需要数组公式):=CONCAT(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)), MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))注意:CONCAT函数在Excel2016及以后才有,如果更早版本,可以使用CONCATENATE,但CONCATENATE不支持数组,因此需要复杂的方法(如使用辅助列)。方法三:使用VBA自定义函数按Alt+F11打开VBA编辑器,插入模块,输入以下代码:```vbaFunctionExtractNumbers(cell AsRange)As StringDim sAs String, iAs Integers =cell.ValueFori =1To Len(s)IfMid(s, i,1)Like "[0-9]"ThenExtractNumbers =ExtractNumbers& Mid(s,i,1)End IfNext iEnd Function```然后在单元格中使用=ExtractNumbers(A1)即可。由于用户询问的是函数,所以这里主要介绍公式方法。但注意,公式方法在旧版Excel中可能较为复杂,且需要数组公式。另外,还有一种情况:如果字符串中只有一组数字(可能包含小数点,如金额),那么上述方法提取数字可能不包含小数点。如果需要小数点,可以修改判断条件。针对包含小数点和负号的情况,可以调整判断条件。例如,在VBA中修改Like条件为"[0-9.-]",但这样可能会把非数字中的减号也当作负号,需要根据具体情况调整。公式方法也可以扩展:在判断是否为数字时,可以增加对小数点和负号的判断,但公式会变得更复杂。因此,根据需求,如果只需要整数数字,则上述公式即可;如果需要带小数点的数字,则需要在判断中加入小数点。下面给出一个支持提取带小数点的数字的公式(同样需要数组公式,且适用于Excel2016及以上):=TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))+(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="."),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1), ""))注意:这个公式将小数点也包含进来,但是负号没有包含。如果包含负号,需要进一步调整。同时,这个公式可能会将多个小数点都包含进来,所以需要根据实际字符串结构判断是否适用。由于不同情况下的字符串结构不同,提取数字需要根据具体情况调整。总结:在Excel提取单元格中的数字,可以使用数组公式或VBA自定义函数。对于普通用户,推荐使用TEXTJOIN的数组公式方法(如果版本支持)。否则,考虑使用VBA。下面我们按照要求组织答案:1.使用TEXTJOIN函数的数组公式(支持整数和小数点,但不支持负号):=TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))+ (MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="."),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1), ""))注意:输入公式后按Ctrl+Shift+Enter(Excel365可能不需要)。2.使用VBA自定义函数(支持自定义需要提取的字符,如数字、小数点、负号等):按Alt+F11,插入模块,复制以下代码:```vbaFunction ExtractNumbers(cell AsRange)As StringDim sAs String, iAs Integers =cell.ValueFori =1To Len(s)IfMid(s, i,1)Like "[0-9.-]" Then'这里将数字、小数点和负号都包含ExtractNumbers= ExtractNumbers &Mid(s, i,1)EndIfNextiEndFunction```在单元格中使用=ExtractNumbers(A1)注意:VBA方法提取负号时,如果负号不在数字开头,可能不符合数字表示,需要根据实际需求调整。由于用户问题中没有说明数字是否包含负号和小数点,这里提供了两种常见情况的处理。最后,根据要求,我们需要在最后添加相关问题部分。</think>### Excel提取单元格中数字的函数方法 #### 方法1:使用数组公式提取数字 适用于Excel 2016+(需`CTRL+SHIFT+ENTER`输入数组公式): ```excel =TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"")) ``` - `MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)` 拆分每个字符 - `ISNUMBER(--...)` 检测是否为数字 - `TEXTJOIN` 连接结果 #### 方法2:基础函数组合提取固定位置数字 ```excel =--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1)) ``` - `FIND({0,1,2,3,4,5,6,7,8,9},...)` 定位首个数字位置 - `MID` 截取数字部分 - `--` 转换为数值 > 适用场景:数字位于字符串开头 #### 方法3:正则表达式提取(VBA自定义函数) 1. 按`ALT+F11`打开VBA编辑器 2. 插入模块后粘贴代码: ```vba Function ExtractNum(cell As Range) As Double Dim regex As Object Set regex = CreateObject("VBScript.RegExp") regex.Pattern = "[-+]?\d*\.?\d+" regex.Global = True If regex.Test(cell.Value) Then ExtractNum = regex.Execute(cell.Value)(0) Else ExtractNum = 0 End If End Function ``` 3. 工作表中调用:`=ExtractNum(A1)` > 支持提取含小数/负号的数字 #### 方法4:快速填充(Excel 2013+) 1. 在B1手动输入A1中的数字 2. 选中B列按`CTRL+E` 3. 系统自动识别模式填充 > 需初始示例,适合非公式需求 #### 特殊场景处理 - **提取货币值**:`=VALUE(SUBSTITUTE(SUBSTITUTE(A1,"$",""),",",""))` - **分离文本数字**:`=FILTERXML("<a><b>"&SUBSTITUTE(A1," ","</b><b>")&"</b></a>","//b[.*0=0]")`(需Excel 2013+)
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值