当前位置:首页 > 办公 > Excel教程 > 正文内容

excel怎么同时定位字符串中的第一个和最后一个数字

酷网3周前 (10-07)Excel教程22

在很多情况下,我们都面临着需要确定字符串中第一个和最后一个数字的位置的问题,这可能是为了提取包围在这两个边界内的子字符串。然而,通常的公式都是针对所需提取的子字符串完全由数字组成,如果要提取的数字中有分隔符(例如电话号码)则无法使用。当然,可以先执行替换操作来去掉字符串中的分隔符,这可能会更复杂些。

本文仅涉及被提取的字符串内包含唯一的数字子字符串的情况。

我们以示例来解。先看一下要提取的数字中没有分隔符的情形,例如在单元格A1中的字符串如下:

Account No. 1234567890: requires attention

显然,我们要提取出1234567890。

下面是我们曾经使用的一个公式:

=-LOOKUP(1,-(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))&”**0″))

注意,必须在MID函数生成的值的末尾添加“**0”,以保证能够在任何情况下都得到正确的结果。例如,如果单元格A1中的字符串是:

Account No. 12-Jun: requires attention

使用没有添加“**0”的公式:

=-LOOKUP(1,-(MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))))))

返回的结果不是12,而是43994,即日期2020-6-12对应的序数。连接字符串“** 0”后,确保类似于“12–Jun”的字符串变为“12–Jun**0”,这样Excel不会将它们认为数字。同样,这也适用于与科学记数法格式的数字相似的字符串。

当然,这样的字符串还必须具有使任何数字保持不变的特性。字符串“**0”等效于“E0”,即表示索引为0的科学计数法,与10 ^ 0一致,因此可保证以这种形式表示的任何数字都将是不变。可以在工作表中进行下列测试来验证:

=0+(147&”**0″)

返回147。

=0+(147&”**2″)

返回14700。

这种方式比“E0”更好,例如:

=-LOOKUP(1,-(MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))))&”E0″))

得到的结果是36689,因为提取的子字符串为“12-JunE0”,Excel认为是日期2000-6-12。并且,“E”在不同的环境中可能有不同的解释。

好了!下面让我们看看一个相似的例子,但要提取的子字符串数字中包含有分隔符:

Account No. 1-234-5678-90: requiresattention

使用上面给出的公式:

=-LOOKUP(1,-(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))&”**0″))

返回1,而不是我们想要的1-234-5678-90。

正如上文提出的,先删除分隔符并不是一件简单的事:

=-LOOKUP(1,-(MID(SUBSTITUTE(A1,”-“,””),MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))&”**0″))

乍一看似乎可以,但返回的结果是1234567890。留给我们的是,如何在正确的位置重新插入分隔符?当然,如果所给字符串的格式是固定的,例如电话号码。然而,即便如此,使用多个REPLACE/SUBSTITUTE函数可能使公式更复杂。

本文寻找的是如何通过确定字符串中的第一个和最后一个数字来提取出子字符串的一种通用解决方案,而不管分隔符是什么、有多少,并且不需要执行替换操作。

在前面的一系列文章中,我们已经找到了一种非常合适的方法来确定字符串中第一个数字的位置,即MIN/FIND函数组合构造。然而,找到一种等效的用于确定字符串中最后一个数字的结构并不容易,能够实现这一点是关键。

对于MID函数的参数num_chars:

=MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17)),[someconstruction])

假设希望避免[some construction]由两个单独的子句进行减法运算,其中一个是字符串内第一个数字的位置,另一个是最后一个数字的位置。我们首先查看一些确定字符串中最后一个数字的位置的公式结构,然后查看其中的哪一个(如果有的话)也可能有助于发现第一个数字的位置,这可能会很有用。下面是实现此目的的3种主要的公式结构:

公式1:

=MATCH(1,0/ISNUMBER(0+MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)))

公式2:

=LOOKUP(1,0/ISNUMBER(0+MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))

公式3:

=MAX(IF(ISNUMBER(0+MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))

其中,公式1和公式3是数组公式。

显然,最好的是公式1,因为它不需要重复ROW子句。但是,这样的构造还可以用于查找字符串中的第一个数字吗?如果不行,公式2可以吗?公式3呢?

我们先尝试减法运行,即使用确定最后一个数字位置的子句减去用于确定第一个数字位置的子句:

MATCH(1,0/ISNUMBER(0+MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)))-MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17))

从而构成解决方案:

=MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17)),1+MATCH(1,0/ISNUMBER(0+MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)))-MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17)))

不错!但是,可以改进参数num_chars的构造吗?

一种方法是对上面给出的公式3:

=MAX(IF(ISNUMBER(0+MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))

进行微小的调整。在2010年及以后的版本中,Excel提供了AGGREGATE函数,它不仅可使许多数组(CSE)结构转换为非CSE,而且还具有标准的CSE公式无法复制的其他优点。

与公式3等价的使用AGGREGATE函数的公式为:

=AGGREGATE(14,6,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))/ISNUMBER(0+MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)),1)

我们可以利用其来不只生成最大值或最小值,而是生成包含这两个值的数组。因此,构造公式:

=MMULT(AGGREGATE({14,15},6,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))/ISNUMBER(0+MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)),1),{1;-1})

转换为:

=MMULT({25,13},{1;-1})

其中的13和25分别代表字符串中第一个和最后一个数字的位置。

但是,其仍有一点缺陷,就是需要重复ROW结构。我们能否对此进行改进,找到不需要重复子句的公式构造?是的,可以使用:

MATCH(“*”,T(1/(1+MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1))),{1,0})

这类似于AGGREGATE的结构,将一个数组传递给其参数,得到两个结果组成的数组。上面的公式转换为:

MATCH(“*”,T(1/(1+{“A”;”c”;”c”;”o”;”u”;”n”;”t”;””;”N”;”o”;”.”;””;”1″;”-“;”2″;”3″;”4″;”-“;”5″;”6″;”7″;”8″;”-“;”9″;”0″;””;”r”;”e”;”q”;”u”;”i”;”r”;”e”;”s”;””;”a”;”t”;”t”;”e”;”n”;”t”;”i”;”o”;”n”})),{1,0})

转换为:

MATCH(“*”,T(1/({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;2;#VALUE!;3;4;5;#VALUE!;6;7;8;9;#VALUE!;10;1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!})),{1,0})

转换为:

MATCH(“*”,T({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;0.5;#VALUE!;0.333333333333333;0.25;0.2;#VALUE!;0.166666666666667;0.142857142857143;0.125;0.111111111111111;#VALUE!;0.1;1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}),{1,0})

转换为:

MATCH(“*”,{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;””;#VALUE!;””;””;””;#VALUE!;””;””;””;””;#VALUE!;””;””;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!},{1,0})

可以看出,不是错误值的就是数字值。指定参数match_type的值为1将为提供数组中最后一个非#VALUE!的位置;为0将提供第一个非#VALUE!的位置。这样,上面公式转换成:

{25,13}

现在,可以将此数组传递给MMULT函数,以最终得出MID函数的参数num_chars参数的值。最终的公式为:

=MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1/17)),1+MMULT(MATCH(“*”,T(1/(1+MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1))),{1,0}),{1;-1}))

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/39450.html

分享给朋友:

相关文章

Excel基础函数IF的7个使用技巧,硬核干货哦!

Excel基础函数IF的7个使用技巧,硬核干货哦!

IF函数,恐怕是大家接触的最早,用的最多的一个函数了吧……如果你仅仅用它来做简单的判断,那就有点儿浪费IF函数了…… 一、IF函数:基础功能及语法结构。 功能:判断给定的条件是否成立,如果成立,返回指定的一个值,否则返回...

Excel图表根据规定好的占比划分成绩等级

Excel图表根据规定好的占比划分成绩等级

问题情境 如下成绩样表: 需要给每位同学的成绩划分等级,而且,规定好了各等级所占的人数百分比:倒数10%以内为D级、10%~40%为C级、40%~80%为B级、剩余为A级。 公式实现 在D2输入公式: =LOOKUP(...

COUNTIF+OFFSET:统计动态区域内数字出现的个数

COUNTIF+OFFSET:统计动态区域内数字出现的个数

问题情境 如下样表: 左侧为彩票开奖记录区,右侧为记录数字出现次数的区域。 要求: 随着彩票开奖记录的逐条增多,实现数字的出现次数自动变化。 公式实现 在I2单元格输入公式: =COUNTIF(OFFSET($B$2,...

LOOKUP函数又一典型应用——根据抽样标准计算抽样数量

LOOKUP函数又一典型应用——根据抽样标准计算抽样数量

某单位对产品进行抽样检查,各产品数量不同,要依据抽样标准抽取样本数量进行质检。 抽样标准表、各产品数量如下: 要求计算出各产品抽样数量。 公式实现 在I3单元格输入公式: =LOOKUP(H3,$B$3:$B$15,$...

COUNTIF+OFFSET:统计动态区域内数字出现的个数

COUNTIF+OFFSET:统计动态区域内数字出现的个数

问题情境 如下样表: 左侧为彩票开奖记录区,右侧为记录数字出现次数的区域。 要求: 随着彩票开奖记录的逐条增多,实现数字的出现次数自动变化。 公式实现 在I2单元格输入公式: =COUNTIF(OFFSET($B$2,...

如何恢复Excel内的文件?Excel重要数据恢复方法

如何恢复Excel内的文件?Excel重要数据恢复方法

  我们日常办公的时候经常会将一些重要的数据保存在Excel文件并储存在电脑中,但是有的时候我们会不小心将Excel文件误删掉,或者因为其他软件导致Excel文件损伤丢失。那么我们应该...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。