如何把数字提取出来 / 开普饭

送人玫瑰，手有余香，请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径！

我们在日常的工作中遇到的最多的问题无外乎就是文本处理、统计求和查找引用了。今天就继续和大家分享一道文本处理方面的问题。

请看下图，如何将数字从字符串中提取出来呢？

观察一下源数据，数据结构并不复杂，所有的数字都位于文本的左侧。处理起来比较简单的。

下面我们一起来看看几种常用的处理方法。

首先我们来看看LOOKUP函数。

在单元格C2中输入公式“=-LOOKUP(1,-LEFT(A2,ROW($1:$9)))”，回车后向下拖曳即可。

思路：

LEFT函数部分，从第一个字符开始，依次向右提取9个字符。这里ROW($1:$9)的含义是1-9个数。为什么要$1:$9而不是$1:$10或其它数值呢？其实只要这部分大于最长的那个字符串的长度就可以了。这部分的结果是{"3";"32";"32公";"32公里";"32公里";"32公里";"32公里";"32公里";"32公里"}
-LEFT(A2,ROW($1:$9))部分将数值转换为负数，结果为{-3;-32;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
利用LOOKUP函数来查找“1”，“1”这个参数的设定很重要。根据LOOKUP函数的特点，查找不到“1”，就会返回最后数值“-32”
最后在利用依次“-”，得到正确的结果

这里第一个参数可以设置为“0”吗？答案是不可以。因为在一些特殊的情况下，比如说文本前的数值是“00002”这种形式的，查找结果就会出货。你知道是为什么吗？

接下来要登场的SUBSTITUTE函数。

在单元格C2中输入公式“=--LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},""))))”，三键回车后向下拖曳即可。

这个解法稍微有些难度，我们来仔细分析一下。

思路：

SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},"")部分，利用SUBSTITUTE函数，用空（“”）来替代单元格A2中文本字符串中的任意数字
完成后利用LEN函数求得替代后的字符串长度
用替代前的字符串长度分别减去替代后的字符串的长度。如果有替代，差值就会大于等于1；否则，差值就是0。替代了几个数字，差值就是几
将上面的差值求和，就是所有被替代的数字的个数，也就是文本字符串中我们要提取的数字的位数
利用LFET函数提取数值

下面的这个方法最简单，CTRL+E，一秒钟解决问题的！

但这个方法有一定的局限性，它要求源数据有一定的规律，才能够得到正确的结果。

下面将要出场的将是LEN/LENB这对函数。

在单元格C2中输入公式“=--LEFT(A2,2*LEN(A2)-LENB(A2))”，回车后向下拖曳即可。

思路：

这时候，MID函数举手表示它也行的。

在单元格C2中输入公式“=--MID(A2,SEARCHB("?",A2),2*LEN(A2)-LENB(A2))”，并向下拖曳即可。

这里用到了另一个比较重要的技巧。SEARCHB("?",A2)表示在A2的文本字符串中搜索第一个单字节字符的位置。这里“?”就代表了单字节。

其余的比较简单，不再详细讲了。

最后，再来介绍一下TEXT函数吧。

在单元格C2中输入公式“=MAX(--TEXT(MID(A2,1,ROW($1:$9)),"0;;;!0"))”，三键回车并向下拖曳即可。

思路：

MID(A2,1,ROW($1:$9))部分，和上面的例子相似，表示从第一个字符开始，依次提取2个、3个...，字符串，其结果为{"3";"32";"32公";"32公里";"32公里";"32公里";"32公里";"32公里";"32公里"}
接下来着重讲一讲TEXT函数。这里它的第二个参数"0;;;!0"设置了数值具体的显示方式。它的含义是，对上面MID函数的结果，强制将正数显示为正数本身；负数和零显示为“0”，文本强制显示为“0”，其结果为{"3";"32";"0";"0";"0";"0";"0";"0";"0"}
后面的步骤就很简单了，不再详细介绍了。

今天和大家分享了几种数字提取的方法。如果大家有兴趣，可以参看帖子细说数据拆分这点事，看完本篇后就都明白了，这里有更多的关于这方面的知识！

-END-

长按下方二维码关注EXCEL应用之家

面对EXCEL操作问题时不再迷茫无助

我就知道你“在看”

如何把数字提取出来