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

excel从字符串中提取数字——数字位于字符串末尾

酷网1周前 (10-07)Excel教程14

上篇文章解了提取位于字符串开头的数字的公式技术,本文研究从字符串开头提取数字的技术:

1. 这些数字是连续的

2. 这些连续的数字位于字符串的末尾

3. 想要的结果是将这些连续的数字返回到单个单元格

与上篇文章一样,对于下面研究的每种解决方案,我们需要在两种不同的情况下测试其健全性:

1. 字符串中除末尾外其他地方没有数字的情况,例如ABC456。

2. 字符串中除末尾外其他地方也有数字,要么在开头,要么在中间,例如123ABC456或ABC123DEF456。

MIN与FIND

公式1:

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

如果单元格A1中的内容为“ABC456”,那么上述公式1返回“456”。其解析过程如下:

=0+MID(“ABC456”,MIN(FIND({0,1,2,3,4,5,6,7,8,9},“ABC456” & 1/17)),LEN(“ABC456”))

在A1后面添加1/17与在其后面添加字符串“0123456789”效果相同。主要是必须将A1与包含0到9的所有数字的字符串连接起来,以防止MIN函数返回错误。因为如果A1不包含0-9的所有数字,那么FIND函数的返回值至少有一个是错误值,由于MIN函数不会忽略错误值,会导致整个公式的结果也是一个错误值。通过首先在A1中的字符串末尾添加一个适当的值(例如1/17、3^45,“0123456789”),可以保证该字符串将包含至少一个0-9中的每个数字(因此FIND函数的所有10个返回值都是非错误值),同时通过将字符串放在A1的末尾确保我们不会影响MIN函数所需的输出。

这样,现在上述公式转换为:

=0+MID(“ABC456”,MIN(FIND({0,1,2,3,4,5,6,7,8,9},”ABC456″&0.0588235294117647)),LEN(“ABC456”))

可能你还不知道,在某些公式结构中,MIN函数具有强制返回数组的能力,且无需按Ctrl+Shift+Enter组合键。本例就属于这种情况,因此FIND函数将对传递给它的所有10个值进行操作,而不仅仅是对第一个值进行操作。因此,上述公式转换为:

=0+MID(“ABC456”,MIN({7,19,13,14,4,5,6,21,11,17}),LEN(“ABC456”))

MIN函数返回字符串中数字开始的位置4,因此,上述公式转换为:

=0+MID(“ABC456”,4,LEN(“ABC456”))

注意,MID函数(以及RIGHT函数和LEFT函数)具有一些有用的特性,当传递的参数num_chars的值大于要提取的实际字符串的长度时不会出错。也就是说,如果该参数的值将导致全部或部分返回值超出字符串末尾的情况时,则这些函数仅返回从指定位置到字符串末尾的那些字符。这里将参数num_chars的值指定为LEN(A1),因为没有子字符串的长度会大于字符串自身的情况。这样,上述公式转换为:

=0+MID(“ABC456″,4,6)

得到结果:

456

下面我们看看如果单元格A1中的字符串包含其它数字例如123ABC456,公式1是否有效。此时公式1转换为:

=0+MID(“123ABC456“,MIN(FIND({0,1,2,3,4,5,6,7,8,9},”123ABC4560.0588235294117647”)),LEN(A1))

转换为:

=0+MID(“123ABC456“,MIN({10,1,2,3,7,8,9,24,14,20}),LEN(A1))

转换为:

=0+MID(“123ABC456“,1,LEN(“123ABC456“))

转换为:

=0+MID(“123ABC456“,1,9)

由于字符串中有其它数字的影响,导致找到的传递到MID函数的起始位置变化,因此结果为:

#VALUE!

LOOKUP与RIGHT

公式2:

=-LOOKUP(0,-RIGHT(A1,ROW(INDIRECT(“1:”& LEN(A1)))))

这个公式与上一篇文章《Excel从字符串中提取数字——数字位于字符串开头》中的公式1相似,只是使用RIGHT函数代替了LEFT函数。

当然,如果单元格A1中的字符串与“JAN18”、“123E4”类似,则不会返回正确的结果,详细原因参见上篇文章。

RIGHT、MATCH与ISNUMBER

公式3:

=0+RIGHT(A1,MATCH(FALSE,ISNUMBER(0+MID(A1,1+LEN(A1)-ROW(INDIRECT(“1:”& LEN(A1))),1)),0)-1)

这是一个数组公式。

仍以单元格A1中的数据为“ABC456”,公式3可转换为:

=0+RIGHT(“ABC456”,MATCH(FALSE,ISNUMBER(0+MID(“ABC456”,1+6-ROW(INDIRECT(“1:”& 6)),1)),0)-1)

转换为:

=0+RIGHT(“ABC456”,MATCH(FALSE,ISNUMBER(0+MID(“ABC456”,1+6-{1;2;3;4;5;6},1)),0)-1)

转换为(注意,这里是如何将得到的数组颠倒,从而使得从字符串的末尾到开头逐个获取字符成为可能):

=0+RIGHT(“ABC456”,MATCH(FALSE,ISNUMBER(0+MID(“ABC456”,{6;5;4;3;2;1},1)),0)-1)

转换为:

=0+RIGHT(“ABC456”,MATCH(FALSE,ISNUMBER(0+{6;5;4;C;B;A}),0)-1)

通过适当的数学运算(将数组与0相加),强制数字字符转换成数字:

=0+RIGHT(“ABC456”,MATCH(FALSE,ISNUMBER({6;5;4;#VALUE!;#VALUE!;#VALUE!}),0)-1)

ISNUMBER函数忽略传递给其参数中的错误值:

=0+RIGHT(“ABC456”,MATCH(FALSE,{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE},0)-1)

转换为:

=0+RIGHT(“ABC456”,4-1)

其中的4表示在字符串“”中第一个非数字字符自右向左出现的位置,减1表示数字字符出现的位置:

=0+RIGHT(“ABC456”,3)

结果为:

456

下面,我们来看看字符串中除末尾以外的数字会不会影响最终的结果。例如如果单元格A1中的数据是“123ABC456”,那么公式3可以转换为:

=0+RIGHT(“123ABC456”,MATCH(FALSE,ISNUMBER(0+MID(“123ABC456”,1+LEN(“123ABC456”)-ROW(INDIRECT(“1:”& LEN(“123ABC456”))),1)),0)-1)

转换为:

=0+RIGHT(“123ABC456”,MATCH(FALSE,ISNUMBER(0+{“6”;”5”;”4”;”C”;”B”;”A”;”3”;”2”;”1”}),0)-1)

转换为:

=0+RIGHT(“123ABC456”,MATCH(FALSE,ISNUMBER(6;5;4;#VALUE!; #VALUE!; #VALUE!;3;2;1}),0)-1)

转换为:

=0+RIGHT(“123ABC456”,MATCH(FALSE,{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE},0)-1)

转换为:

=0+RIGHT(“ABC456”,4-1)

其中的4表示在字符串“”中第一个非数字字符自右向左出现的位置,减1表示数字字符出现的位置:

=0+RIGHT(“ABC456”,3)

结果为:

456

可以看到,由于是从右向左取数,因此在字符串的其它位置存在数字并不会影响结果。

RIGHT、MATCH与ISERR

公式4:

=0+RIGHT(A1,MATCH(1,0+ISERR(0+MID(A1,1+LEN(A1)-ROW(INDIRECT(“1:”& LEN(A1))),1)),0)-1)

这是一个数组公式。

以单元格A1中的数据为“123ABC456”,公式4可转换为:

=0+RIGHT(“123ABC456”,MATCH(1,0+ISERR(0+{“6″;”5″;”4″;”C”;”B”;”A”;”3″;”2″;”1″}),0)-1)

转换为:

=0+RIGHT(“123ABC456”,MATCH(1,0+{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE},0)-1)

转换为:

=0+RIGHT(“123ABC456”,MATCH(1,{0;0;0;1;1;1;0;0;0},0)-1)

转换为:

=0+RIGHT(“123ABC456”,4-1)

转换为:

=0+RIGHT(“123ABC456”,3)

结果为:

456

与公式3一样,字符串中除末尾有数字外其它部位存在数字时不影响结果。

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

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

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

分享给朋友:

相关文章

怎么使用Microsoft Excel制作表格

怎么使用Microsoft Excel制作表格

除了电子表格软件的其他功能,Microsoft Excel还为你提供了在工作表内创建表格的功能。在Excel 2003里被称为“列表”,它们可以独立于该页面以及工作表其他页面中的数据进行操作。阅读下面的步骤1,了解如何在...

在Excel中如何换行?Excel表格换行快捷键是什么?

在Excel中如何换行?Excel表格换行快捷键是什么?

  在Excel中如何换行?Excel表格中每一个单元格的显示范围都是有限的,如果我们想要表格完整显示,必然将某个表格进行设置换行处理,换行有两种方法,一种是Excel自动识别换行,另...

Word转Excel的方法 Word如何转换成Excel

Word转Excel的方法 Word如何转换成Excel

  Word是我们经常有用到的办公软件之一,有时候也会用Word来编辑表格。Word转Excel就是将Word中制作好的表格转成Excel表格,用Word编辑转成Excel的时候该要怎...

Excel不显示网格线怎么办?Excel不显示网格线的解决方法

Excel不显示网格线怎么办?Excel不显示网格线的解决方法

  Excel是一款功能强大的电子图表工具,在工作中我们经常会使用到它。最近有用户向小编资讯一个问题,在Excel表格中,竟然不显示网格线,让用户很是烦恼。大家都知道在正常的Excel...

怎么清除Excel函数保留数据?Excel去公式保留数据的方法

怎么清除Excel函数保留数据?Excel去公式保留数据的方法

  怎么清除Excel函数保留数据?这里的函数说的是Excel计算公式,往常我们需要使用公式计算出来的数据其实挺麻烦的,好在这样的机会不多,如果你想知道Excel去公式保留数据的方法,...

Excel中如果能有这个函数,中国式排名就很简单了!

Excel中如果能有这个函数,中国式排名就很简单了!

要根据学生的考试成绩计算下排名,直接用Excel中内置的函数RANK,结果如下:   有两位同学为并列第四,可之后就直接第六名了,第五名不见了,这并不符合我们的习惯,从小到大,每次考试后并列的名次很常见,可是不...

发表评论

访客

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