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)。

Like this post? Share on: Twitter ❄ Facebook ❄ Email