Excel中如何提取数字？会这几招就够了

途人

有读者留言希望能够整理一期内容，讲解从数字、汉字中提取汉字或者数字的方法，本文将对比较常见的几类提取情况进行讲解。使用公式提取单元格内的数字，要根据具体的数据结构找到某种规律从而设计出对应的公式。当然也有所谓的万能提取公式，不过非常复杂并且计算量大，因此只在文末简单说明。
第一类情况：数字在左侧

• 例一：数字在左侧，且数字位数固定。
  

在这个例子中，数据非常有规律，数字都在左边的三位，要想将学号单独提取出来，只需要使用left函数即可：=LEFT(A2,3)



公式也非常简单，第一个参数是要提取数字的单元格，第二个参数是要提取几位（从左边算起）。对于例一的这一类数据，使用的时候根据数字的长度修改第二参数即可。

• 例二：数字在左侧，数字位数不固定，但是有明显的分隔符号。
  

如果数字长度不是固定的三位，使用之前的方法就不行了，如下图。这时就需要找到数据源的规律，再利用规律进行操作。当前的数据统一存在一个"-"号，可以根据"-"号的位置来确定数字的长度，再用left提取。



对于这种情况需要用到另一个函数来帮忙，那就是find函数。
利用公式=FIND("-",A2,1)可以确定"-"的位置。



find函数有三个参数，第一个参数是要找什么内容，第二个参数是从哪里找，第三个参数是从第几个字开始找（从左边算起）。
=FIND("-",A2,1) 意思是在A2单元格找“-”，从开始位置找（左边第一位）。公式的结果是数字，代表“-”在单元格的位置（第几个字符），此时我们需要提取的数字长度就是find的结果减1，因此方法就有了：=LEFT(A2,FIND("-",A2,1)-1)



这个方法的适用范围也比较广泛，只要可以发现比较明显的分隔符号（可以是符号，汉字，字母等等内容），都可以使用这个方法。

• 例三：数字在左边，位数不固定并且没有明显的分隔符号。
  

如果数据是这种情况，那么前面的方法都不能用了，数字长度不确定，也没分隔符号，唯一的规律就是数据里只有数字和汉字（没有字母以及其他符号）。
在Excel中，字符的长度有两种，半角字符（数字，字母以及英文方式下输入的符号）长度为1，而全角字符（汉字和中文符号等）长度为2。可以通过一个实例来了解这一点：



len函数是一个专门计算单元格内容长度的函数，不区分全角半角，其长度与我们平时理解的一致，相当于字符的“个数”。下面再来看看区分全角和半角的情况：



lenb函数的作用于len函数一致，区别就是计算内容的长度时会区分半角全角，从结果可以看出明显的差异。其中文字按全角计算的长度是不区分全半角的2倍，而数字、字母在两种情况下则完全一致。
对于例三的情况，就需要使用len与lenb来确定数字的长度，再用left提取。
利用公式=LEN(A2)*2-LENB(A2)可以确定数字的长度，如下图所示。



为什么len*2-lenb就会得到数字的长度呢？
根据前面两种情况下的对比结果，lenb统计长度，文字长度是len统计值的两倍，而数字长度与len统计值一致，所以公式len*2-lenb就可以计算出数字的长度了。
数字的长度统计出来以后，解决方法显而易见：=LEFT(A2,LEN(A2)*2-LENB(A2))



例三这种情况同样比较典型，只要记住是len*2-lenb就可以了，如果确实记不住，那么遇到问题的时候可以单独算一算，先把数字的长度算出来，再组合left就行了。

• 例四：数字在左边，并且含有字母等半角字符。
  

这种情况很复杂，唯一的规律就是数字在左边。来看看如何解决吧。
可以利用公式：=-LOOKUP(1,-LEFT(A2,ROW($1:$9)))。



关于这个公式，要解释的话估计还得五千字，所以各位只需要记得套路即可，唯一有可能修改的就是最后面那个9，当最长的数字超过9位时，就要修改这个9，可以直接用99来完成。
最后，这个公式适用于任何数字在左边的情况。
第二类情况：数字在右边
数字在右边的情况与数字在左边非常类似，以下仅做简单说明：
数字位数固定的直接用right提取；



数字位数不固定但有分隔符号的使用find函数配合mid函数 完成提取：=MID(A2,FIND("-",A2,1)+1,9)



（注：公式中最后一个参数值“9”需要根据数字的最大位数修改，譬如提取的数字最大位数是10，则需要写成10或者大于10的数字。）
数字位数不固定同时没有分隔符的还是可以使用len和lenb的组合：=RIGHT(A2,LEN(A2)*2-LENB(A2))



什么规律都没有的继续让lookup发大招：=-LOOKUP(1,-RIGHT(A2,ROW($1:$9)))



第三类情况：数字在中间
数字在中间这种非常特殊，比较规范的数据源中一般不会出现这样的情况，举一个例子吧：



例如图中这种数据，如果数字前面或者后面的长度固定，可以先用函数将内容变成前面讨论过的情况再去处理：



公式比较简单，不做解释了，至于后面一步怎么做，根据前面的学习你已经会了吧？
下面要说的这个方法就是一种比较通用的方法了，有人称其为万能提取公式：
=-LOOKUP(0,-MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1/17)),ROW($1:$9)))



数组公式，需要按三键“Ctrl+shift+回车”结束。
小结
关于如何使用公式提取单元格内的数字，以上将绝大多数情况都进行了说明，当然不是全部，例如含有小数的情况，因为有了小数点，就更加特殊了。
最后的那个数组公式可以算是一个比较通用的公式了。需要强调的是：大部分的问题之所以变得非常麻烦，就是因为不规范的数据源导致的，因此养成好的习惯，一个单元格只存放一种属性的数据，例如把名称与数量、价格等信息分开存放，这样就会大大地提高统计效率。
当然站在提高公式运用能力的角度来说，数字提取这一类问题也是函数练习的好题目。希望大家能够结合以前学过的函数，思考出更多的公式来实现数字的提取。
来源：部落窝教育                作者：老菜鸟

凤兮丶梦兮

麻烦.用jxl几行代码就可以解决问题

山水一笑

大佬能不能解释一下原理啊

唯思琪

大佬能不能解释一下row函数啊，我试验了一下，不能得到你那个结果啊，只能把find 里面的row函数,换成｛1，2，3，...，9｝，A1&“012...9”。

九坪

我只是搬运工，你可以加QQ群

涨留住

645620236

弘揚

感谢作者  有了这个超级方便的，有句话说excel什么都能除了不能生孩子   哈哈哈，我百度里查到这条  特意上app来评论  感谢作者。[爱][爱][爱]

青水浮萍

row函数返回引用单元格的行号，如果是区域的话，会选择区域内所有行的行号数组。
find函数中的ROW($1:$10)，返回结果就是{1,2,...,10}，后面又减去1，结果就是{0,1,...,9}。
所以row函数是构造等差数列数组的简洁方法。

不过你这里将{0,1,...,9}改为{1,2,...,9}存在问题：如果数字首位是0，就会被忽略。
比如“abc012def”中只能获取到“012”，不过这个例子是无所谓的，因为最终是数值类型的结果，所以“012”和“12”的结果都是12。
问题是，当提取小于1的小数时就会出现严重问题。
比如“abc0.12def”，本应该获得数值0.12，但你的算式只能获取到12了。

对不起我是保安

最后一个提取数字的算式在某些场合下存在问题。
您的算式是：
-LOOKUP(0,-MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1/17)),ROW($1:$9)))

这里lookup的第一个参数查找值您设置成了0，而第二个参数的数组虽然设置了负数，但也可能为0。

lookup在查找时，使用的是二分查找，就是先对数组元素按由小到大排序，之后获取中间的元素进行比较：
如果与中间元素相同，结果就是中间值；
如果小于中间元素，则将查找范围设置为中间元素之前的数组，继续使用二分查找
如果大于中间元素，则将查找范围设置为中间元素之后的数组，继续使用二分查找

那问题就出来了，如果数组中超过一半的元素都是0的话，lookup检索的结果就只能是0。

比如：“学员：001阳阳”，用您的公式获取的结果就是：0。
再加一位“学员：0012阳阳”，结果还是0，因为这时没有中间位，就选择了第二位。
当变为“学员：00123阳阳”时，结果就正确了：123

修改的方法很简单，将第一个参数由0改为1：
-LOOKUP(1,-MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1/17)),ROW($1:$9)))

蓅煋

很棒 感谢