Excel中8个必会的高效提取数字法
Excel中8个必会的高效提取数字法
在Excel数据处理中，经常需要从混合的文本中提取数字。本文将介绍8种高效提取数字的方法，包括数字与汉字、字母混合等常见场景，帮助你快速掌握这一实用技能。
数字与汉字
数字与汉字之间有分隔符
使用Ctrl+E和分列的方法大家都很熟悉，这里不再赘述。
我们还可以使用FIND函数查找分隔符。在C2单元格输入以下公式，最后下拉填充就搞定啦：
=LEFT(B2,FIND("-",B2)-1)
小解析：利用FIND函数找到分隔符所在的位置，再用LEFT函数从左边开始提取数字。
数字与汉字之间没有分隔符
数字在汉字左侧
和上文一样，也可以用快捷键Ctrl E进行分列。
我们还可以借助LENB+LEN函数，获取位置信息、提取数字。如下图中，LEN函数将英文作为1个字节，而LENB函数将汉字作为2个字节。
因此，我们在F2单元格中输入以下公式，下拉填充即可：
=LEFT(E2,LEN(E2)*2-LENB(E2))
小解析：LEN(E2)*2-LENB(E2)就是获取左边数字的个数，搭配LEFT函数截取左边的数字。
数字在汉字右侧
当数字在汉字右边时，我们可以用函数LEN+LENB+MID。
输入公式如下：
=MID(H2,LENB(H2)-LEN(H2)+1,99)
小解析：LENB(H2)-LEN(H2)主要是获得左边文本的个数，加上1后就是首个数字出现的位数。从首个位置开始截取到99位（具体提取的位数只要大于等于数字的最大位数即可）。
数字在汉字中间
需要利用LOOKUP函数配合数组公式来做。公式如下：
=-LOOKUP(1,-MIDB(K2,SEARCHB("?",K2),ROW($1:$15)))
小解析：这里是利用SEARCHB函数的通配符查找功能，查找字符串中的数字。由于公式是一个数组公式，需要按Ctrl＋Shift＋Enter进行数组运算。
数字与字母汉字混合
一旦涉及到数字、字母和汉字一起混合的情况，我们就需要使用复杂数组公式了。数组公式需要按住进行执行数组运算，公式左右两侧会出现大括号；如果你是最新的预览版Office 365，直接按Enter就可以。
提取字符串在左右侧的连续数字
数字在最右侧
=-LOOKUP(1,-RIGHT(O2,ROW($1:$15)))
数字在最左侧
=-LOOKUP(1,-LEFT(R2,ROW($1:$15)))
小解析：首先使用RIGHT函数，从右到左分别截取长度为1到15的子字符串，再利用取负运算，将文本转换为错误值，将数值转换为负数；然后利用LOOKUP函数获取数组的最后一个值，最后将负数取负转为正数。从左侧提取的数字原理，跟从右侧提取的原理相同。
数字在任意位置
借助LOOKUP函数
=-LOOKUP(0,-MID(K2,MIN(FIND(ROW($1:$10)-1,K2&1/17)),ROW($1:$15)))
小解析：
- 首先利用
ROW函数构造0-9的数字数组：ROW($1:$10)-1 1/17等于0.0588235294117647，它是一个包含0-9所有数字的值，连接在文本的尾部是避免FIND函数在查找数字时，因文本缺少相应数字而返回错误值。- 用
FIND函数查找10个数字在文本中的位置，利用MIN函数返回文本中出现数字的最小位置，即可以得到首个数字的位置。 - 利用
MID函数从首个数字位置开始，依次向右截取长度为1~15的15个子字符串，加上负号，将数字转化为负数，文本转化为错误值。 - 最后利用
LOOKUP函数返回最大的数字，再使用负号将负数转化为正数。
借助MAX函数（只能提取整数）
公式如下：
=MAX(IFERROR(--RIGHT(LEFT(K20,ROW($1:13)),COLUMN(13)),COLUMN(13)),COLUMN(A:$D)),0))
小解析：
- 先用
LEN函数确定字符串中最长的个数。 ROW(1:13)——13这个数只要大于字符串中最长的个数即可，目的是是后面一个个子字符的截取。LEFT(K20,ROW($1:$13))——从首个字符开始依次从右截取1~13个子字符串。COLUMN(A:D)——A:D取决于字符串中数字的最大位数。RIGHT(LEFT(K20,ROW($1:13)),COLUMN(13)),COLUMN(13)),COLUMN(A:$D)——从左开始向右依次截取113个子字符，再从此数组的基础上从右开始依次向左截取14个子字符，此时是一个13*4的数组。- 公式前加
--，是为了将文本转为错误值。 - 将错误值转换为0，从数组选出最大值，即可获得最大数字。