知方号

知方号

Excel 中 8 个必会的高效提取数字法<用公式提取出需要行的内容>

Excel 中 8 个必会的高效提取数字法,你知道几个?

01、数字与汉字 ▋数字与汉字之间有分隔符 【Ctrl+E】& 分列的方法大家都很熟悉啦,这里就不再赘述~

Excel 中 8 个必会的高效提取数字法,你知道几个?

这里我们还可以使用 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】进行数组运算。

02、数字与字母汉字混合 一旦涉及到数字,字母和汉字一起混合的情况,我们就需要使用复杂数组公式了。

数组公式需要按住进行执行数组运算,公式左右两侧会出现大括号;

如果你是最新的预览版 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 ) ) , C O L U M N ( 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 ) ) , C O L U M N ( 13)),COLUMN ( 13)),COLUMN(A:$D)—— 从左开始向右依次截取 1~13 个子字符,再从此数组的基础上从右开始依次向左截取 1~4 个子字符,此时是一个 13*4 的数组。

❺ 公式前加–,是为了将文本转为错误值。

❻ 将错误值转换为 0,从数组选出最大值,即可获得最大数字。

转载与https://www.ithome.com/0/646/177.htm

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至lizi9903@foxmail.com举报,一经查实,本站将立刻删除。