excel从文字中提取数字

excel从文字中提取数字

提取数值的万能公式情景特征：除了目标数值，文本中不存在其他数字，否则容易产生干扰。万能公式：{=-LOOKUP(9^9,-MIDB(A2,MIN(FINDB(LEFT(ROW($1:$11)-2,1),A2&-1/19)),ROW($1:$100)))}公式详细拆解如下：①LEFT(ROW(1:11)-2,1)ROW(1:11)很好理解，返回第1行到第11行的行号，也就是11个字符组成的集合A{1,2,3…11}，-2则变为字符集B{-1,0,1,2…9}。再通过LEFT提取字符集B左侧的第一个字符，生成字符集C{"-",0,1,2,…9}，也就是符号和0-9这十个字符，所有数值，均由这11个字符构成。综上，该部分的功能就是构建阿拉伯数字全部字符，这些数字有助于我们锁定位置，进而提取阿拉伯数值。②FINDB(①,A2&-1/19)FINDB是查找字符所在目标文本中的位置，它与FIND的差异是，它返回字节序号，即把汉字和中文符号视为2个字节。由此可知，A2单元格混森者型合文本中，负号“-”出现的位置是5，而不是3。该公式中使用了A2&-1/19是为了确保字符集C{"-",0,1,2,…9}的每一个字符均在FIND的查找文本中出现，确保FIND的返回值不存在错误值。片段②返回字符集C{"-",0,1,2,…9}在A2&-1/19出现的位置，即序数集D{5,13,10,6,…}。③MIN(②)MIN(②)取②的结果序数集D{5,13,10,6,…}中的最小值，它就是目标数值在A2中的起始位置，即A2混合文本中，首次出现负号或阿拉伯数字的位置，即是目标提取数值的起始位置。这就是为什么要求目标数字的左侧，不能有无关的阿拉伯数字或负号的原因。④-MIDB(A2,③,ROW($1:$100))这里使用MIDB，而不是MID，是为了对应FINDB，通过字节位置截取部分文本。ROW($1:$100)返回有序数组{1-100}，作为MIDB函数的第三个参数——要提取的字节数，即分别提取1-100个字符。于是，MIDB函数的功能就是从③确定的起始位置开始，分别从A2单元格文本中截取长度为1-100个字节的100个不等长字符串E{"-","-2","-29","-299",…"-299.19"}。而-MIDB则是将不等嫌差长字符串执行减法运算，使得非数值数据因无法运算而报错为#VALUE!，进而将不等长字符串E转化为纯数字和错误值#VALUE!组成的新常量数组F{#VALUE!;2;29;299;299;299.1;299.19;…;299.19}⑤-LOOKUP(9^9,④）LOOKUP查询有三个特性：1．默认查询区域是升序的，即越往后值越大。2．返回值应小于且最接近于查询此猜值。3．忽略查询区域中的错误值。由此，我们赋予查询值一个极大数9^9，因为LOOKUP的特性1，所以查询区域的最后一个非错误值为最大值，即该值为返回值。LOOKUP的这几个特性，完美地做到了忽略错误值取最后一个有效值！五、提取字符的万能公式用法：依次提取目标单元格的全部数值并合并。万能公式：{=SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW($1:$100),1))*ROW($1:$100),ROW($1:$100))+1,1)*10^ROW($1:$100)/10)}公式简要拆解如下：① ISNUMBER(--MID(A2,ROW($1:$100),1))*ROW($1:$100)通过MID(A2,ROW($1:$100),1)逐一提取每一个字符，使用双负号运算，区分数字和其它字符，再使用ISNUMBER函数判断每一个字符是否为数字，返回一组逻辑值，最后*ROW($1:$100)使得数字返回其在A2混合文本中的位置，其他字符返回0。② LARGE(①,ROW($1:$100))通过LARGE函数，将①中的字符位置值集合从大到小重新排序。由于数字在文本中的位置总是大于0，且数字越靠后，位置值越靠前。而其他字符总是小于0的。这里的重点是将所有的0值置后，同时将所有数字位置值倒排。③ MID(0&A2,②+1,1)MID根据②的位置值+1从0&A2中逐一取数。由于非数字的位置值为0，所有非数字返回值均取首位0，其余数字不受影响。由于②的数字位置值是颠倒的，所以，此时提取出的数字前后也是颠倒的。④ SUM(③*10^ROW($1:$100)/10))前三步得到了A2单元格中的所有数字和一串代表非数字位置的0组成的有序数组，此时要完成最终的提取，还需要将数字正序排列、去除0值并将其合并。这些通通交由*10^ROW($1:$100)/10完成，它通过构建一个多位数来将各个数字顺序摆放，最终将代表文本的有效数位前的0值省略，其余数字按次序从个位开始向左排列。最终的多位数即数字提取结果。其实，提取数字字符串的问题，19年以后版本有了一个很简单又不烧脑的解决方案––通过CONCAT直接连接就行了。19版万能公式如下：{=CONCAT(IFERROR(--MID($A2,ROW($1:$100),1),""))}公式简要说明：1.使用MID和ROW组合，将每一个字符逐一提取出来。2.通过双负号区分数字和非数字，非数字将报错。3.IFERROR将非数字错误值转化为空。4.使用CONCAT函数将所有数字合并。