本文链接:http://oicu.cc.blog.163.com/blog/static/1230394712009852119363/
vba的方法在前面已经有两篇日志中提到,本文只讨论用函数法。
A列为字符,B列为函数。
类似 Google docs 的 spreadsheet 中使用:
=ArrayFormula(join("",IF(ISNUMBER(1*MID(A1,ROW($1:$99),1)),MID(A1,ROW($1:$99),1),"")))
即可提取到数字,只是gg表格中做动态长度比较麻烦,所以定义向量的长度为99,结果是字符型的。
Excel中因为没有join连接函数,所以比较麻烦,下面两个数组公式都只能用于这三种形式
的字符串:abc123/123abc/abc123def (所有数字连续)
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$99),1)),0),COUNT(1*MID(A1,ROW($1:$99),1)))
原理:确定第一个数字在字母数字字符串中的位置及数字总数,然后mid返回此数字及其后的数字。
如果字符串是 a0123b 这种形式,想得到结果 0123,则不要用 1 乘以整个公式。
=-LOOKUP(,-MID(A1,MATCH(,MID(A1,ROW($1:$99),1)*0,),ROW($1:$99)))
原理:match找到第一个为数字的位置
-mid从上述位置开始取字符,并转换为负数值
lookup用0找到最小的数值
-lookup负数取正
不用乘以1,结果已经是数值型的了,缺点:0在最前面、没有数字时出错
上面的公式只能用于长度小于99的字符串,可以把ROW($1:$99)替换为准确的向量长度:
=ROW(INDIRECT("$1:$"&LEN(A1)))
或者
=ROW(INDIRECT(CONCATENATE("$1:$",LEN(A1))))
得到最后的结果:
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1)),0),COUNT(1*MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1)))
=-LOOKUP(,-MID(A1,MATCH(,MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1)*0,),ROW(INDIRECT("$1:$"&LEN(A1)))))
第一个公式的分步解释(都是数组公式,为了演示方便,长度设置为9):
ROW($1:$9)
定义一个1~9的数组
MID(A1,ROW($1:$9),1)
分解字符,构造向量,
得到的是字符型,ISNUMBER(MID(A1,ROW($1:$9),1))的结果都为FALSE
1*MID(A1,ROW($1:$9),1)
字符转化为数字,非数字的转为为#VALUE!
ISNUMBER(1*MID(A1,ROW($1:$9),1))
判断数字
COUNT(1*MID(A1,ROW($1:$9),1))
统计数字个数
MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0)
查找到第一个数字的位置
最后mid即可。
附图(点击查看大图):
评论