新浪博客

如何提取文本中的连续数字，MID&FIND函数妙用【公式解析】

2017-06-07 10:41阅读：

http://blog.sina.cn/dpool/blog/u/1426389024

提取文本中的连续数字，公式为 =MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&'0123456789')),2*LEN(A2)-LENB(A2))，至于公式的每个部分是啥意思，有啥讲究，且看下文。别怕，不难。
本文涉及的函数：
  • MID——文本提取
  • MIN——提取数组中的最小值
  • FIND——查找指定内容在文本中的位置
  • LEN——计算文本的字符数
  • LENB——计算文本的字节数


一天上午，阳光明媚的上午，忽然没来由地想一件事：怎么从文本中提取连续的数字呢？
如何提取文本中的连续数字，MID&FIND函数妙用【公式解析】

号码纯属瞎编，别当真
解决这类技术性问题，你可以先：
  • 搜百度
  • 搜论坛
注：ExcelHome是国内资深论坛，应有尽有
这俩的区别在于，常见的问题搜百度几乎都能找到答案，而专业性强，难度大，更个性化的问题，搜论坛会更加准确。而且，论坛还可以带附件提问，相对方便。不过，论坛上由于很多人反复问过同样的问题，你的问题如果太普遍，一般很难等到有人再答复。
公式 =MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&'0123456789')),2*LEN(A2)-LENB(A2))


就是从百度搜来的，我们要分析的，也正是这个公式。
原本写到这里，应该给大家说说搜索的技巧。但今天再一搜，忽然发现我已经找不到曾经看过的这个答案，真是见了“鬼”。于是，一个新的体会分享给大家——搜索，靠的是缘分。没开玩笑，搜索常常能得到很多答案，能不能遇见最优的解法，有时候，真的看缘分。


不信，你看：
如何提取文本中的连续数字，MID&FIND函数妙用【公式解析】
如何提取文本中的连续数字，MID&FIND函数妙用【公式解析】


所以，平日点滴的积累，看见好的用法赶紧记录下来，也是学好Excel的懒人心法。
好了。
我们来说说：


公式 =MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&'0123456789')),2*LEN(A2)-LENB(A2))
要从文本“你说305-1是你家门牌号”中提取数字，首先想到用“文本函数”，文本函数有LEFT，RIGHT和MID，但MID其实包含了LEFT和RIGHT的使用场景，所以，无论连续的数字在文本最左边，最右边，或者在中间，都可以用MID进行提取。
MID函数有三个参数：从什么文本提取，从第几位数开始提取，提取几位数


如何提取文本中的连续数字，MID&FIND函数妙用【公式解析】
第一步
第一参数是好写的 =MID(A2,……）


第二步
第二参数不好写：从第几位数开始提取
这是本例最困难的数据处理逻辑：怎样才能知道每个不同文本中的连续数字是从第几位数开始的呢？


如何提取文本中的连续数字，MID&FIND函数妙用【公式解析】
坦率讲，大多数人都很难想到其中的逻辑，包括我。所以，学习Excel，借鉴经验就变得尤其重要。那些被技术大神们开发出来，解决某类问题的函数模型，以及其中关键性的思维逻辑，一旦有机会看到，千万记录下来，记在本子上，笔记类工具上，Word里，都可以，成为“自己的经验”。
MID的第二参数应该是：MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&'0123456789'))
最核心的一步是：A2&'0123456789'这是用“你说305-1是你家门牌号”文本合并“0123456789”文本，组成新文本“你说305-1是你家门牌号0123456789”。然后用FIND函数，把0~9这十个数字，分别拿到新文本“你说305-1是你家门牌号0123456789”中，查找它们的对应位置，生成一个数组。
函数写为：FIND({0,1,2,3,4,5,6,7,8,9},A2&'0123456789')


FIND函数查找的结果分别为：
  • “0”的结果——4
  • “1”的结果——7
  • “2”的结果——16
  • “3”的结果——3
  • “4”的结果——18
  • “5”的结果——5
  • “6”的结果——20
  • “7”的结果——21
  • “8”的结果——22
  • “9”的结果——23


形成数组：{4,7,16,3,18,5,20,21,22,23}


这里利用了FIND函数只能查找到相同内容的第一个内容位置，例如：A2单元格的文本“你说305-1是你家门牌号”，原本就有“3”，FIND函数就只会查找到新文本“你说305-1是你家门牌号0123456789”中的第一个“3”，返回结果“3”（因为数字3在文本中处于第三个位置）。


根据FIND函数的结果能看到，通过这样的设计，就能用MIN函数，提取数组{4,7,16,3,18,5,20,21,22,23}的最小值，而该最小值，正是A2单元格文本的第一个数字位置。
A2&'0123456789'这步设计，是真的精妙。
第三步
第三参数考验函数积累：提取几位数
函数的学习有一个特点——知道就知道，不知道，猜都猜不出来。例如：如果你不知道一个叫BAHTTEXT的函数，你是永远无法想象，Excel还能把数字变成泰语文本。


所以，学习和使用函数，积累足够多的函数名称是首要任务。
要想知道A2单元格的文本中有几个数字，LENB是必备的函数。说一个能让你回忆起上学时计算机考试的概念——1个汉字等于2个字节，1个数字或字母等于1个字节。
LEN函数数的是“字符”，无论汉字还是数字，1个都对应1个字符；LENB函数数的是“字节”，汉字对应2个字节，而数字对应1个字节。
MID的第三参数应该是：2*LEN(A2)-LENB(A2)
文本“你说305-1是你家门牌号”，用LEN函数数，结果为13，用LENB函数数，结果为21。那么，2*LEN(A2) 相当于把对汉字的计数和 LENB(A2) 扯平，但对数字的计数多出1倍，所以，2*LEN(A2)-LENB(A2) 刚好是A2单元格文本中的数字个数。


如何提取文本中的连续数字，MID&FIND函数妙用【公式解析】
提取文本中连续的数字
完整的公式为 =MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&'0123456789')),2*LEN(A2)-LENB(A2))


如何提取文本中的连续数字，MID&FIND函数妙用【公式解析】
—— END ——
关注公众号：【伍老湿Excel】
更多表格运用和数据管理方法

罗辑思维6月Excel公开课招生啦~
时间：6月24日~6月25日
地点：北京（环球贸易中心D座）
谁需要了解企业管理工具Excel：
企业主
核心管理人员
企业中的“表哥”，“表姐”
“大表哥”，“大表姐”
扫二维码了解详情，以及报名参加
如何提取文本中的连续数字，MID&FIND函数妙用【公式解析】

我的更多文章

下载客户端阅读体验更佳

APP专享