Excel数字提取技巧：从混合文本中提取数字的方法

编按：哈喽，大家好！前面我们分享了不用函数公式提取数字的5种方法。今天我们分享用简单公式从混合文本中提取数字的方法。因为采用的公式很简单，所以总体来说只适合数字在文本中的位置有一定规律的情况。如果想用公式提取没有位置规律的数字，那就得看我们下一篇教程。学习更多技巧，请收藏关注部落窝教育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代表一个大于所有文本长度的字符数，不是固定值，可以根据实际情况修改。学习更多技巧，请收藏关注部落窝教育excel图文教程。

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

含半角符两侧不定长混合文本的特征：

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法提取数字。

以上，就是混合文本提取函数公式的三种进阶情景应用。其中的公式均只能在特定条件下生效，缺点明显，缺乏普适性，但简单高效，也较易理解，在观察到对应特征时，小花推荐使用对应公式。

在下篇文章中，小花将为大家介绍并详细拆解提取数值万能公式和提取数字字符串万能公式，有兴趣深入学习的小伙伴请务必吃透今日公式。学习更多技巧，请收藏关注部落窝教育excel图文教程。

＊＊＊＊部落窝教育-excel混合文本提取数字＊＊＊＊

原创：小花/部落窝教育（未经同意，请勿转载）

更多教程：部落窝教育