Excel中提取数字方法

2020年9月11日 分类:Excel 作者:企业网盘

从理论上来说,我们应当避免将数字和文字填写在同一个单元格中,从而产生混合文本,影响进一步的数据处理和分析。但理想很丰满,现实很骨感,由于惯例、系统设定或人员素质等诸多原因,混合文本不可避免。于是,混合文本提取数字,成了很多Excel用户必须面对的问题。

解决这一问题的思路有很多,函数是最主要手段之一。而如何设置函数公式,往往取决于混合文本的特征;观察,是解决问题的最快路径。下面,将和各位花一起,边观察,边解决。

PS:可由LEFT, RIGHT或MID直接截取的简单问题,此处不再赘述。

情景一:简单不定长

简单不定长混合文本的特征: 1.不含英文及其他字符。2.数字统一位于文本最左侧、最右侧或中间固定起始位置。

解决思路: 数字初始位置固定,可以直接用LEFT、 RIGHT或MID提取,无需确认起始位置。唯一需要计算的参数就是文本长度。这里由于混合文本不含单字节字符(英文字符或半角符号),我们可以使用LEN和LENB来确定数字长度。其中,LEN计算总字符数,LENB计算总字节数,由于1个汉字=1个字符=2个字节,1个单字节字符=1个字符=1个字节,于是我们可以用2*LEN-LENB来计算数字的长度,从而完成提取。 PS:2*LEN-LENB确定数字长度的数学逻辑类似鸡兔同笼,小花瓣们可以参照理解。 左侧公式:=LEFT(D2,2*LEN(D2)-LENB(D2))

右侧公式:=RIGHT(A2,2*LEN(A2)-LENB(A2))

中间公式:=MID(G2,3,2*LEN(G2)-LENB(G2))

情景二:特定符号引导

特殊符号引导混合文本的特征: 1.数字位置不固定。2.数字长度也不固定。3.数字有特殊字符引导,且可能存在其他单字节字符。 解决思路: 该情景虽然可以通过FIND函数锁定特殊符号的初始位置,但却因为其他单字节字符的存在,导致情景一中用2*LEN-LENB确认长度的方法无法使用,情景一公式在情景二中宣告失败。 正确的思路是,使用SUBSTITUTE函数将指定符号替换为连续空格字符串(通过REPT函数构建),从而使数字处在足够多个的连续空格之间。再通过MID函数截取“空格+数字+空格”这样的字符串,最后使用TRIM去除多余空值,实现对数字的提取。

PS:数字99代表一个大于所有文本长度的字符数,不是固定值,可以根据实际情况修改。

情景三:含半角符两侧不定长

含半角符两侧不定长混合文本的特征: 1.数字位置在文本两侧。2.数字长度不固定。3.混合文本中含英文字母、半角符号等单字节字符。 解决思路: 该情景虽然可以通过LEFT或RIGHT函数从两侧提取数字,但同样因存在其他单字节字符,无法使用2*LEN-LENB确认数字长度。同时,因为无固定引导符号,使用长空格的设想也就此落空。 在这种情况下,我们可以使用数组的方法,依次提取每一个可能的结果值。如案例中的B2单元格,我们依次从“299.19公斤”中,从左提取1到100个字符,生成“2,29,299,299.,299.1,299.19,299.19公,299.19公斤,299.19公斤,299.19公斤……”等100个不同长度的字符串。 由于数字总在混合文本两侧,所以,目标数字总是所有纯数字的最后一个,例如例子B2中,299.19是最后一个纯数字,也是目标数字。所以,我们使用LOOKUP查询一个极大数字9^9来获取最后一个纯数字。 PS:LEFT前的负号用于将文本型数字转化为数字型数字,而LOOKUP前的数字则用于恢复数值原来的正负性。

如果案例中的数字均为正数,我们还可以使用MAX法来解决问题。这是因为,经过LEFT提取后的一串不等长字符中,由于纯数字都是正数,所以目标数字将同时满足最长、最后且最大的特定,这一点小花瓣们可以通过公式求值进行验证。所以,我们可以通过求最大值来锁定目标值。特别提醒,该公式为数组公式,输入后需按【Ctrl+Shift+Enter】才能正确运算。

这里需要注意的是,由于MAX函数不具备LOOKUP那样剔除错误值的能力,所以我们需要使用IFERROR函数来赋予错误值(经过双负号转换后,文本均显示错误#VALUE!)一个足够小的数字,从而不会影响MAX锁定最大正数。这里我们可以明显的看到B6单元格,当目标数字为负值时,公式出错。这就是MAX法相较于LOOKUP的明显劣势,因此情景三,还是推荐使用LOOKUP法提取数字。最后大家可以将做好的文档保存在坚果云,这样就不怕丢失也不怕需要历史版本,还能方便地将文件共享给其他人。坚果云的企业网盘是非常多人使用的,异次元,黑科技软件,科技大咖都在极力推荐,也推荐给大家。

阅读已结束,喜欢的话就点个赞吧
注册坚果云网盘
还有其他问题,可以咨询小坚果咨询小坚果
继续阅读