下图展示了汉字、英文字母和数字混排的部分源文本,需要提取文本中所有的数字。
提取所有数字
B2 单元格输入以下数组公式,按<Ctrl+Shift+Enter> 组合键。
{=LEFT(TEXT(SUM((0&MID(A2,SMALL(IF((MID(A2,ROW($1:$99),1)>='0')*(MID(A2,ROW($1:$99),1<='9'),ROW($1:$99),100),ROW($1:$15)),1))*10^(15-ROW($1:$15))),REPT(0,15)),COUNT(-MID(A2,ROW($1:$99),1)))}
公式中的“MID(A2,ROW($1:$99),1)”部分,依次提取文本中的单个字符,然后与字符“0”和“9”
比较,使数字字符返回逻辑值TRUE,非数字字符返回逻辑值FALSE。结合IF 函数,使数字字符返回在文本中对应的位置,非数字字符返回位置100(大于文本长度)。
利用SMALL 函数依次提取15 个最小的位置,即数字所在的位置,通过MID 函数返回对应位置上的数字,多余的数组元素返回空文本。
{'0';'7';'8';'9';'1';'0';'5';'0';'';'';'';'';'';'';''}
通过“0”连接上述数字数组,使空文本变成“0”,以参加后续除法运算。用数字数组乘以“10^(15-ROW($1:$15))”,结合SUM 函数求和使数字连接在一起,返回78910500000000。
由于整数会丢失高位的0(零),所以利用TEXT 函数结合REPT 函数来补齐15 位数字,即高位的0(零),返回“078910500000000”。
利用COUNT 函数统计文本中数字的个数,最后通过LEFT 函数返回文本中所有数字的字符串。
通过SUM错位求和的方法,最多只能连接15位数字。超过15位的部分,由于Excel精度
的限制,将被舍弃。如果文本中需要提取的数字超过15位,可结合示例12-15中的迭代计算方式实现。
这些小技巧,你都了解了吗?
---------------------------------------------------------------------
联系客服