登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oh! I see you!

Hi! ppmm~~

 
 
 

日志

 
 

Excel中用函数提取字符串中的数字  

2009-09-05 14:01:19|  分类: Excel |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

本文链接: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即可。

 

附图(点击查看大图):

Excel中用函数提取字符串中的数字 - oicu - Oh! I see you!


 

  评论这张
 
阅读(5363)| 评论(6)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018