excel从字符串中提取数字——数字位于字符串末尾
发布时间:2023-04-05 06:43:57
上篇文章解了提取位于字符串开头的数字的公式技术,本文研究从字符串开头提取数字的技术:
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一样,字符串中除末尾有数字外其它部位存在数字时不影响结果。
猜你喜欢
- 有时候在重启电脑或者更新完Win10电脑之后,鼠标的右键一直在转圈圈,那么如果碰到了鼠标一直在转圈圈该如何去解决呢,下面就和小编一起来看看收
- 系统升级到win10以后,打不开office2013文件,其实出现这种问题跟要打开的文件本身没有多大关系,只是Office 2013到了Wi
- 使用Word编辑表格的时候,我们一定会遇到多页表格使用同一个标题行的情况,这时如果一个一个设置的话,会非常麻烦,同时也会出错。我们可以设置W
- 随着win10版本越来越稳定,众多第三方应用程序很多都已经只支持win10系统,但是有的用户在使用旧版本应用的时候,会提示出错,程序无法运行
- 成对条形图(或旋风图)常用于对比两类事物在不同特征项目的数据情况,例如美国总统选举中不同地区投支持票和反对票的比例情况,足球比赛中主队和客队
- Word 2013表格技巧和提示!在Word 2013中,文本会逐个单元地倒入表格中。您可以输入单词,句子甚至段落。尽管单元格会更改大小以容
- word2010添加多个图片后怎么避免重叠?很多朋友都不是很清楚,其实操作很简单的,下面小编就为大家详细介绍一下,不会的朋友可以参考本文在用
- Word2003是微软公司提供的一款文字处理软件,我们在使用Word编辑一些通告、通知之类的文档的时候,根据排版格式的需要经常会用到纵横混排
- 《Excel2003入门动画教程13、Excel表格自动填充序号》。演示动画 操作步骤如果我们经常需要在Excel
- 今天有朋友发来一篇文档,说他选中目录,点击右键,修改字体与段落,不管怎么修改,一旦“更新整个目录”,就又返回了初始的乱七八糟的状态,如下图:
- 通过自定义单元格格式来处理。Excel版本参考:20101、选中A2:A10单元格区域;2、按下CTRL+1组合键,调出自定义单元格格式对话
- 办公过程中经常会使用WORD工具制作文本,怎样让文本更生动立体呢?下面小编整理关于Word的使用技巧,希望小编整理的能对你有所帮助!关于流程
- 原因:当在函数或公式中没有可用数值时,将产生错误值#N/A。解决方法:如果工作表中某些单元格暂时没有数值,请在这些单元格中输入"#
- excel自定义格式的中0、#、?、,@符号等到底是什么意思,今天我们就来详解介绍个各个占位符的含义和用途,并附带案列。1.G/通用格式这种
- 在我们的数学和物理中,总是有很多复杂的公式符号,那些符号我们可能懂,但是在Word中却不知道要怎么编辑。其实很简单的,感兴趣的用户可跟小编一
- Excel表头怎么制作?这个问题一直困扰很多初学excel的朋友,其实方法很简单,下面这篇文章就为大家带来excel表头斜线制作方法,一起看
- 如果某些单元格区域中要输入的数据很有规律,如学历(小学、初中、高中、中专、大专、本科、硕士、博士)、职称(技术员、助理工程师、工程师、高级工
- 在Excel表格中,求和是我们经常要做的事,那我们该怎样快速又高效的去完成数据求和了?其实关于数据的求和有很多方法,比如普通求和,函数求和等
- excel 2019“指数平滑”分析工具基于前期预测值导出相应的新预测值,并修正前期预测值的误差。此工具将使用平滑常数,其大小决定了本次预测
- Excel中有很多技巧值得学习,可以帮助我们提高办公效率,今天给大家分享五个技巧,总有一个会惊艳到你。1、数字转大写数字要转换成大写的数字怎