Excel從右向左查找

平日學習點滴

Excel從右向左查找

今天看一個小問題，是前幾天遇到的，就是要在Excel裡面實作從右向左查找。比如給定字元串“abc,defg,hi,jkl”，需要找到最右一個逗号的位置（即12），或者最右一個逗号右邊的部分（即“jkl”）。

Excel裡面字元串查找用的FIND函數，隻能從左向右（可以指定起始位置）地進行字元串查找。如果想要找到最右邊的待查字元串，就要稍微費點兒勁兒了。為了友善起見，下面稱待查字元串為分隔符。

如果分隔符的個數是已知且确定的（比如IP位址中的小數點），可以通過多個FIND函數嵌套來實作。但這個其實是從左到右查找第n個。如果分隔符的個數不确定，這個方法就不太合适的。

我的處理方法是這樣的，假設單元格A1存放着包含分隔符的完整字元串（如上面提到的“abc,defg,hi,jkl”），那麼查找最右一個逗号的公式為：

這個公式的結果顯然是12。

看起來很複雜，其實一步步拆解開并不是太難，基本的原理是這樣的：

SUBSTITUTE(A1,",","")：把原字元串中的逗号全部删除（替換成空字元串），得到臨時字元串text1；

LEN(A1)-LEN(text1)：用原字元串的長度減去text1的長度，即可知道原字元串中總共有多少個逗号，num2；

SUBSTITUTE(A1,",",CHAR(1),num2)：利用SUBSTITUE函數，把原字元串中的最後一個逗号替換成特殊字元CHAR(1)，得到臨時字元串text3；

FIND(CHAR(1),text3)：在text3中查找特殊字元CHAR(1)，其位置就是原字元串中最後一個逗号的位置pos。

真是一個奇妙的方法。

找到位置後，要取出左邊或者右邊的内容就很簡單了，公式分别是（用pos代替那個複雜的FIND函數）：=LEFT(A1,pos-1)，=RIGHT(A1,LEN(A1)-pos)。

補充：

上面那個公式隻是适用于單個字元的查找，如果分隔符是多個字元，就需要稍微修改一下。假設單元格B1裡面存放着分隔符本身，那麼公式可以修改為：

唯一的變化就是上述的第2步，原字元串的長度減去text1的長度後，要除以分隔符本身的長度，才是分隔符的個數。

在這種情況下，取（最右分隔符）右邊的子串的公式也要相應地修改為：=RIGHT(A1,LEN(A1)+1-LEN(B1)-pos)。