一个单元格中既包含中文英文又有数字,如何只把数字提取出来,学好这一招就可以了。
实例中我们只考虑在单元格中只有一组数据的前提下,可能包含一下几种情形:
英文+数字+中文
英文+数字+英文
中文+数字+中文
中文+数字+英文
中文+数字
英文+数字
数字+中文
数字+英文
数字
下面直接上公式,如下图所示:
C1=LOOKUP(9E+307,--MID(B2,MATCH(1,(MID(B2,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},1))^0,0),{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}))
这里用到的函数比较多,也用到了数组的概念,我们可以按照剥洋葱的方法由外往里剥
第一层:
LOOKUP(9E+307,数组):
从数组中找到最后的数字,9E+307为一个非常大的数字,当从数组中找不到这个数时,就会返回最后的那个数字。
如:
=LOOKUP(9E+307,{1;10;102;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!})
返回102
第二层:
MID(字符串,起始位置,个数)
从字符串中取出中间的字符,从第几个字符开始取,取几个
如:
=MID("BLOCK102分段",5,2)
返回:”K1”,即从"BLOCK102”字符串中,从第5个字符开始开始取,取两位字符
第三层:
MATCH(要查找的数字,数组,0)
从数组中匹配要查找的数字,返回所在的位置,0为绝对查找,1为模糊查找
如:
MATCH(5,{3;5;8;10})
返回2,即5在数组中的位置
第四层:
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}
这里为定义了一个数组,当然也可以用ROW(1:15)来表示,不过最后要用CTRL+SHIFT+ENTER三键建立数组函数
第五层:
^0
表示0次方
--
表示负负得正,就是通过强制转换将文本型数字变为真正的数字,即把”1”变成1,因为用MID函数取出的是文本类型,在查找数字时会出错。
我们在从最里层往外走:
把B2单元个的每个字符取出来,形成一个数组
MID(B2,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},1)
= {"B";"L";"O";"C";"K";"1";"0";"2";"分";"段";"";"";"";"";""}
对数组的每个字符进行0次方,非0数字的0次方为1,其它为为错误值
(MID(B2,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},1))^0
= {"B";"L";"O";"C";"K";"1";"0";"2";"分";"段";"";"";"";"";""}^0
={#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;#NUM!;1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
从数组中匹配1所在的位置,即第一个非零字符的位置
MATCH(1,(MID(B2,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},1))^0,0)
=MATCH(1, {#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;#NUM!;1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!},0)
=6
从第六个字符开始,依次取1个,2个。。。。。。直到15个字符,形成一个数组,并强制转化成数字格式
--MID(B2,MATCH(1,(MID(B2,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},1))^0,0),{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15})
= --MID(B2,6,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15})
= --{"1";"10";"102";"102分";"102分段";"102分段";"102分段";"102分段";"102分段";"102分段";"102分段";"102分段";"102分段";"102分段";"102分段"}
={1;10;102;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
从数组中找到最后的那个数字
LOOKUP(9E+307,--MID(B2,MATCH(1,(MID(B2,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},1))^0,0),{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}))
= LOOKUP(9E+307,-- {1;10;102;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!})
=102
总结:
先找到第一个数字出现的位置,在从这个位置依次取1个,2个……15个,得到一个数组,再从这个数组中找到最后的那个数字就可以了。
你明白了吗?
前面说过也可以把{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}用ROW(1:15)来表示,则可以把公式修改一下如这个样子:
C1=LOOKUP(9E+307,--MID(B2,MATCH(1,(MID(B2,row($1:$15),1))^0,0), row($1:$15)))
看上去简单一下,但是别忘了最后用CTRL+SHIFT+ENTER三键在公式外面套一个花括号,即:
C1={LOOKUP(9E+307,--MID(B2,MATCH(1,(MID(B2,row($1:$15),1))^0,0), row($1:$15)))}
注意:这个花括号用手动输入时无效的,并且当点击公式编辑栏时花括号会自动消失,所以不建议用。
当然可以按照
C1=LOOKUP(9E+307,--MID(B2,MATCH(1,(MID(B2,row($1:$15),1))^0,0), row($1:$15)))
写公式,然后用鼠标选中row($1:$15),然后按F9键,将其转换成{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}即可
有点啰嗦,不明白的在下方留言。
函数参考:
联系客服