Excel表格里有文字有数字怎样提取数字(电子表格如何提取字符串中的数字)
昨天我们讲了字符串中数字在前面或在后面时的提取办法,今天我们来看下若是数字在中间该怎么办?在解决问题之前我们先学习下会用到的另外两个函数,ISNUMBER,MATCH。
1、ISNUMBER
ISNUMBER(value),检测一个值是否为数字,是就返回TRUE,否则返回FALSE。
如下图:
如果是嵌套使用时检测一个数组区域中是否有数字,结果将以数组的形式输出被引用。
2、MATCH
MATCH(lookup_value, lookup_array, [match_type])
返回该项在此区域中的相对位置,匹配方式分为1,0,-1,分别对应下列情况:
1或者是省略,MATCH 查找小于或等于 lookup_value 的最大值;
0,MATCH 查找完全等于 lookup_value 的第一个值;
-1,MATCH 查找大于或等于 lookup_value 的最小值。lookup_array 参数中的值必须按降序排列,例如:TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ... 等等。
实例如下图:
3、数字提取
学习了上面的函数后,我们来解决下今天的问题。如下图,提取数字。
我们先以不含小数点的数字提取为例。
思路:从字符串中间开始提取字符用MID函数,所以我们只需确定从第几位开始提取,一共提取几位字符,只要确定了这两个参数,问题就迎刃而解了。
从第几位开始提取?昨天刚学习了lookup,他能找到字符串中最后一个数字出现的位置,所以在B1中输入:
=lookup(1,-mid(A1,ROW($1:$10),1),ROW($1:$10))
把A1中的字符串拆解成{"你";"好";"4";"5";"m";"g";"";"";"";""},由于拆解出来的数字被当作了文本,前面加个负号就变成了{"你";"好";-4;-5;"m";"g";"";"";"";""},然后用lookup以1为查找值就会查找到-5,返回对应的行号即4,意思是字符串中最后一个数字在第四位。
用count对字符串中的数字进行计数
=COUNT(-MID(A1,ROW($1:$10),1)),返回2,代表字符串中有两个数字。
最后用mid函数
=mid(A1,4-2 1,2)即可得到想要的结果,中间参数的意思是最后一个数字的位置减去数字位数,然后加1,即得到了开始提取的位数,比如A1中,最后一个数字在第四位,有两位数字,所以从4-2 1=3位开始提取。
公式整合到一起即:
=MID(A1,(LOOKUP(1,-MID(A1,ROW($1:$10),1),ROW($1:$10))-COUNT(-MID(A1,ROW($1:$10),1)) 1),COUNT(-MID(A1,ROW($1:$10),1)))
三键结束。
那找出从第几位开始提取还有没有好的办法呢?
我们用今天学的函数来思考下,
用ISNUMBER来判断A1中是否有数字
=ISNUMBER(-MID(A1,ROW($1:$10),1))返回
{FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
用MATCH来找到第一个TRUE的位置,即找到了第一位数字的位置
=MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$10),1)),0)
用COUNT计算出数字位数。
整合在一起即:
=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$10),1)),0),COUNT(1*MID(A1,ROW($1:$10),1)))
三键结束
这个公式比上面的公式精炼点吧,但是其实本质是一样,都是用MID函数实现提取。
那对于数字中有小数的该怎么提取呢?
这个就不多介绍了,只需要用IF 做个判断,用上面的方法就可以了。
当然如果你用的是16版的excel,那么只需要CTRL E瞬间就可以实现了。
转自:米宏Office