是否需要将你的电脑中的一堆文档合并成一个超大文档？是否想要不通过很多繁琐的复制粘贴操作就把文档的最新版本和原始版本整合在一起？微软办公软件能够合并整合文档，这样你就能够轻易地管理你的文档库和各种修订版......
2023-05-29 0 doc,Microsoft Word,word下载,word官网,word排版教程,word文档,Excel教程,word教程
excel从字符串中提取数字——数字位于字符串末尾
Excel教程
2023-05-29 22:26:19
上篇文章解了提取位于字符串开头的数字的公式技术，本文研究从字符串开头提取数字的技术：
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)
这是一个数组公式。
=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一样，字符串中除末尾有数字外其它部位存在数字时不影响结果。
标签： Excel函数 excel函数公式 excel表格制作 Excel教程 excel 提取 数字 末尾
相关文章
- 详细阅读
-
获取每行中第一个非空单元格详细阅读
如何使用公式获取每行中第一个非空单元格？例如下所示工作表，要求使用公式根据上半部分的表格求各项目的开工日期。 也就是说，要求出单元格区域B2:G6的每行中第一个非空单元格对应的B1:G1中的日期......
2023-05-29 0 excel公式,excel技巧,excel教程,Excel表格,Excel教程
-
如何在Excel 2007中添加下拉框详细阅读
在Excel 2007中添加下拉框后，可以给用户提供一个数据项目列表，让用户更快速地输入数据，而不是一个个地输入它们。当你在一个单元格里插入下拉框之后，单元格就会显示一个箭头。你可以点击箭头，选择目标......
2023-05-29 0 excel公式,Excel函数教程,excel技巧,excel教程,Excel表格,Excel教程
-
excel数据透视表之VBA运算符详细阅读
还记得小时候学习数学的经历吗？很小很小的时候，我们会接触到数字，大人们会教我们数数，认识简单的数字，慢慢地我们会开始学习简单的加减法，再大一点，会学习乘除法，背诵乘法口诀，随着学习的深入，我们会逐渐学......
2023-05-29 0 Excel函数,excel函数公式,excel表格制作,Excel教程
-
如何通过Excel将联系人导入安卓手机详细阅读
本指南介绍如何通过Excel CSV（逗号分隔值）文件将联系人导入安卓手机的通讯录。如果安卓手机无读取CSV文件，可将它导入Google帐户，然后导出为vCard，这样就能与安卓兼容。完成后，可通过G......
2023-05-29 0 excel公式,excel技巧,excel教程,Excel表格,Excel教程