shu 2022-05-04 11:20:32 经常在群里或者公众号见有人问,在一串文字中提取数字的办法,那么笔者总结了一下,在文字中数字的位置无非分三种情况:在头部、中间及最后面,那么我们下来分别介绍一下用法: 1、数字在前面的数字与汉字的分离: B2输入公式:=-LOOKUP(9E+307,-LEFT(A2,ROW($1:$10))) C2输入公式:=RIGHT(A2,LEN(A2)-LEN(B2)) 其中:Lookup的作用是查找小于或者等于他第一参数的数值; 9E+307是传说中Excel能支持的最大的数值,可以直接输入9e307会自动转换; row($1:$10)的作用就是生成序列1-10; left分别提取1-10个数字,然后用减号强制转换成了数值; 最后用Len来判断字符串长度,达到分离出汉字的目的; 因为在取数字的时候,用了负号进行强制转换数字,所以在结果再加一个负号,将提取到的数值转换一下正负;这样做的目的就是能同时提取正负数。 2、数字在后面的数字与汉字分离,其实原理是一样的,看下面的案例: B2输入公式:=-LOOKUP(9E+307,-RIGHT(A2,ROW($1:$10))) C2输入公式:=LEFT(A2,LEN(A2)-LEN(B2)) 所有的意义其实都差不多,只是将LEFT换成了Right而已。 3、数字在字符串的中间,怎样提取数值(这种方式,一般只针对提取正数): 公式为:=-LOOKUP(0,-MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1/17)),ROW($1:$10))) 这是一个数组公式,需要按CTRL + SHIFT+ENTER结束公式的录入。 其中:LOOKUP的作用如上面的一样; ROW($1:$10)-1的目的就是生成0-9这十个数字; Find的作用就是分别找0-9这十个数字,在此字符串出现的位置; Min的作用就是将FIND找到的十个位置,取最小值,也就是第一次出现的地方; 因为FIND函数查找不到的时候,会显示错误值,而我们也没有办法保证0-9这10个数字全部在字符串中出现,所以在查找位置会&1/17这么一个奇怪的表达式,是因为1/17会产生一个无限不循环小数,这个小数里面会包含0-9这10个数字(另外1/19也会达到同样的效果,不要问我为什么会知道,这完全是试出来的)。我们看看: 当然,想任意提取各种数字、文字、字母的组合,只要有规则的话,都是能做到的,最好的办法就是通过VBA调用正则表达式来实现。对于一般的用户而言,上面三种办法已经完全能达到我们想要的了。 大家若有其他好的办法,也是可以留言告诉我的哟。 |
|