Excel 从右向左查找

发现我已经有几乎整整一年没更新博客了。在 Google Reader 都快要关闭的时代,个人博客还有多少存在的意义呢。

今天看一个小问题,是前几天遇到的,就是要在 Excel 里面实现从右向左查找。比如给定字符串“abc,defg,hi,jkl”,需要找到最右一个逗号的位置(即 12),或者最右一个逗号右边的部分(即“jkl”)。

Excel 里面字符串查找用的 FIND 函数,只能从左向右(可以指定起始位置)地进行字符串查找。如果想要找到最右边的待查字符串,就要稍微费点儿劲儿了。为了方便起见,下面称待查字符串为分隔符。

如果分隔符的个数是已知且确定的(比如 IP 地址中的小数点),可以通过多个 FIND 函数嵌套来实现。但这个其实是从左到右查找第 n 个。如果分隔符的个数不确定,这个方法就不太合适的。

我的处理方法是这样的,假设单元格 A1 存放着包含分隔符的完整字符串(如上面提到的“abc,defg,hi,jkl”),那么查找最右一个逗号的公式为:

=FIND(CHAR(1),SUBSTITUTE(A1,",",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))

这个公式的结果显然是 12。

看起来很复杂,其实一步步拆解开并不是太难,基本的原理是这样的:

  1. SUBSTITUTE(A1,",",""):把原字符串中的逗号全部删除(替换成空字符串),得到临时字符串 text1;
  2. LEN(A1)-LEN(text1):用原字符串的长度减去 text1 的长度,即可知道原字符串中总共有多少个逗号,num2;
  3. SUBSTITUTE(A1,",",CHAR(1),num2):利用 SUBSTITUE 函数,把原字符串中的最后一个逗号替换成特殊字符 CHAR(1),得到临时字符串 text3;
  4. FIND(CHAR(1),text3):在 text3 中查找特殊字符 CHAR(1),其位置就是原字符串中最后一个逗号的位置 pos。

真是一个奇妙的方法。

找到位置后,要取出左边或者右边的内容就很简单了,公式分别是(用 pos 代替那个复杂的 FIND 函数):=LEFT(A1,pos-1)=RIGHT(A1,LEN(A1)-pos)

补充:

上面那个公式只是适用于单个字符的查找,如果分隔符是多个字符,就需要稍微修改一下。假设单元格 B1 里面存放着分隔符本身,那么公式可以修改为:

=FIND(CHAR(1),SUBSTITUTE(A1,B1,CHAR(1),(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1)))

唯一的变化就是上述的第 2 步,原字符串的长度减去 text1 的长度后,要除以分隔符本身的长度,才是分隔符的个数。

在这种情况下,取(最右分隔符)右边的子串的公式也要相应地修改为:=RIGHT(A1,LEN(A1)+1-LEN(B1)-pos)

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注