湖南龙网

  1. 首页 > 生活百科 > >

excel公式技巧：从字符串中提取数字——数字位于字符串末尾

生活百科小知识

本文研究从字符串开头提取数字的技术：
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”效果相同 ， 详见《Excel公式技巧08. 17分之一和其他全数字(pandigitals)》 。主要是必须将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

推荐阅读


上一篇：格力定频显示e6是什么故障

下一篇：Excel公式技巧：将所有数字分别提取到不同的单元格