Excel公式-提取字符串中首次出现的数值

最新推荐文章于 2024-04-23 23:03:35 发布

原创最新推荐文章于 2024-04-23 23:03:35 发布 · 5.5k 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#excel

excel 专栏收录该内容

24 篇文章

订阅专栏

本文介绍了如何使用Excel中的LOOKUP、MID和FIND函数组合来智能提取文本数据中的年龄信息。首先，通过FIND函数确定数字出现的位置，接着用MID函数截取相应位置的字符，最后利用LOOKUP函数提取最后一个有效数值，从而实现对记录中采访人物年龄的精确提取。

一、提取公式

=LOOKUP(9^9,--MID(B2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B2&5^19)),ROW($1:$99)))

二、分析过程
1、需求如下：提取如下记录中采访人物的年龄
在这里插入图片描述
2、提取思路
1）我们需要提取出“自我介绍”列的数值信息，即年龄信息；
2）首先获取数字首次出现的位置：

公式：=MIN(FIND({1,2,3,4,5,6,7,8,9,0},B2&5^19))
其中5^19的值为19073486328125，该值中同时包含了从0-9的10个阿拉伯数字，B2值与5^19合并是为了确保此处FIND()函数不报错。
3）我们获取了数字首次出现的位置，接下来从数字首次出现的位置使用MID()函数依次提取1，2，3，4…99位的数据。
在这里插入图片描述
公式：=MID(B2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B2&5^19)),ROW($1:$99))
这个部分的计算得到一个内存数组：
{‘2’;‘22’;‘22岁’;‘22岁’，;…;‘22岁’，‘现在是一名学生，喜欢唱歌和跳舞’;#VALUE!;…;#VALUE!}
4）通过减负运算，将内存数组里的内容转为数值。
在这里插入图片描述
公式：=–MID(B2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B2&5^19)),ROW($1:$99))
转化后的内存数组如下所示：
{2;22;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;…;#VALUE!}
5）最后使用lookup()函数提取该数组中最后出现的数值，即可提取年龄信息，其中lookup函数默认不处理#VALUE!值。
在这里插入图片描述
注：lookup((9^9,A:A),用于提取A列最后出现的值。语法如下：
LOOKUP(一个比查询范围同类数据都大的值，单列或单行查询范围，单列或单行结果范围)，lookup((9^9,A:A)等同于lookup((9^9,A:A,A:A)