原文: Excel公式 提取檔案路徑字尾
我們在代碼中擷取一個檔案路徑的字尾，是一個很簡單的事.
如C#中，可以通過new FileInfo(filePath).Extension，或者Path.GetExtension(filePath)擷取。
而Excel的大資料中，直接批量擷取同行單元格内容中檔案路徑的字尾，該如何擷取？
如上，想擷取檔案字尾，然後從中分析出檔案的所屬開發語言。
公式如下：
1、擷取字尾
=RIGHT(D2,LEN(D2)-FIND("@",SUBSTITUTE(D2,".","@",LEN(D2)-LEN(SUBSTITUTE(D2,".",)))))
過程分析：
我們需要先找到最後的一個字元"."的位置,這樣才能通過Right函數截取相應的長度，擷取字尾。
而找字元，我們用Find函數就行了。但是怎麼找到那唯一的一個呢？
我們通過先通過LEN(D2)-LEN(SUBSTITUTE(D2,".",)查找字元串中有多少個同樣的字元"." ,然後這個數量也就可以代表最後一個字元的順序。
最後一個字元找到了，我們可以SUBSTITUTE(D2,".","@",LEN(D2)-LEN(SUBSTITUTE(D2,".",)))将它替換成一個在這段内容中不會出現的字元@。
然後直接找字元@就行了，FIND("@",SUBSTITUTE(D2,".","@",LEN(D2)-LEN(SUBSTITUTE(D2,".",))))，找到字元@的位置，再用字元串的總長度減去位置，就能得到字元從右往左的位置。
通過Right函數截取，即可。
2、字尾得到了，再判斷是何語言。一個所屬開發語言的項目，包含的檔案類型都是有規律的。
如C#裡面，類檔案是cs。資料庫sql檔案，字尾是sql。
而我們在Excel中隻要用if就能判斷出來。
=IF(ISBLANK(D2),"",IF(OR(RIGHT(D2,LEN(D2)-FIND("@",SUBSTITUTE(D2,".","@",LEN(D2)-LEN(SUBSTITUTE(D2,".",)))))="frm",RIGHT(D2,LEN(D2)-FIND("@",SUBSTITUTE(D2,".","@",LEN(D2)-LEN(SUBSTITUTE(D2,".",)))))="bas",RIGHT(D2,LEN(D2)-FIND("@",SUBSTITUTE(D2,".","@",LEN(D2)-LEN(SUBSTITUTE(D2,".",)))))="cls"),"VB",IF(RIGHT(D2,LEN(D2)-FIND("@",SUBSTITUTE(D2,".","@",LEN(D2)-LEN(SUBSTITUTE(D2,".",)))))="cs","C#",IF(OR(RIGHT(D2,LEN(D2)-FIND("@",SUBSTITUTE(D2,".","@",LEN(D2)-LEN(SUBSTITUTE(D2,".",)))))="c",RIGHT(D2,LEN(D2)-FIND("@",SUBSTITUTE(D2,".","@",LEN(D2)-LEN(SUBSTITUTE(D2,".",)))))="h"),"C++",IF(RIGHT(D2,LEN(D2)-FIND("@",SUBSTITUTE(D2,".","@",LEN(D2)-LEN(SUBSTITUTE(D2,".",)))))="sql","SP",IF(RIGHT(D2,LEN(D2)-FIND("@",SUBSTITUTE(D2,".","@",LEN(D2)-LEN(SUBSTITUTE(D2,".",)))))="4gl","4gl",IF(OR(RIGHT(D2,LEN(D2)-FIND("@",SUBSTITUTE(D2,".","@",LEN(D2)-LEN(SUBSTITUTE(D2,".",)))))="bat",RIGHT(D2,LEN(D2)-FIND("@",SUBSTITUTE(D2,".","@",LEN(D2)-LEN(SUBSTITUTE(D2,".",)))))="sh"),"BAT","UnKnown"))))))) View Code
注意一下異常情況，如單元格為空，需要加個判斷IsBlank或者SUBSTITUTE(D2," ","")=""等
案例Demo：
language公式：
=IF(ISBLANK(E2),"",IF(OR(RIGHT(E2,LEN(E2)-FIND("@",SUBSTITUTE(E2,".","@",LEN(E2)-LEN(SUBSTITUTE(E2,".",)))))="frm",RIGHT(E2,LEN(E2)-FIND("@",SUBSTITUTE(E2,".","@",LEN(E2)-LEN(SUBSTITUTE(E2,".",)))))="bas",RIGHT(E2,LEN(E2)-FIND("@",SUBSTITUTE(E2,".","@",LEN(E2)-LEN(SUBSTITUTE(E2,".",)))))="cls"),"VB",IF(RIGHT(E2,LEN(E2)-FIND("@",SUBSTITUTE(E2,".","@",LEN(E2)-LEN(SUBSTITUTE(E2,".",)))))="cs","C#",IF(OR(RIGHT(E2,LEN(E2)-FIND("@",SUBSTITUTE(E2,".","@",LEN(E2)-LEN(SUBSTITUTE(E2,".",)))))="c",RIGHT(E2,LEN(E2)-FIND("@",SUBSTITUTE(E2,".","@",LEN(E2)-LEN(SUBSTITUTE(E2,".",)))))="h"),"C++",IF(RIGHT(E2,LEN(E2)-FIND("@",SUBSTITUTE(E2,".","@",LEN(E2)-LEN(SUBSTITUTE(E2,".",)))))="sql","SP",IF(RIGHT(E2,LEN(E2)-FIND("@",SUBSTITUTE(E2,".","@",LEN(E2)-LEN(SUBSTITUTE(E2,".",)))))="4gl","4gl",IF(OR(RIGHT(E2,LEN(E2)-FIND("@",SUBSTITUTE(E2,".","@",LEN(E2)-LEN(SUBSTITUTE(E2,".",)))))="bat",RIGHT(E2,LEN(E2)-FIND("@",SUBSTITUTE(E2,".","@",LEN(E2)-LEN(SUBSTITUTE(E2,".",)))))="sh"),"BAT","UnKnown"))))))) Project公式：
=RIGHT(LEFT(E2,FIND("@",SUBSTITUTE(E2,"\","@",LEN(E2)-LEN(SUBSTITUTE(E2,"\",))))-1),LEN(LEFT(E2,FIND("@",SUBSTITUTE(E2,"\","@",LEN(E2)-LEN(SUBSTITUTE(E2,"\",))))-1))-FIND("@",SUBSTITUTE(LEFT(E2,FIND("@",SUBSTITUTE(E2,"\","@",LEN(E2)-LEN(SUBSTITUTE(E2,"\",))))-1),"\","@",LEN(LEFT(E2,FIND("@",SUBSTITUTE(E2,"\","@",LEN(E2)-LEN(SUBSTITUTE(E2,"\",))))-1))-LEN(SUBSTITUTE(LEFT(E2,FIND("@",SUBSTITUTE(E2,"\","@",LEN(E2)-LEN(SUBSTITUTE(E2,"\",))))-1),"\",))))) 檔案名公式：
=RIGHT(E2,LEN(E2)-FIND("@",SUBSTITUTE(E2,"\","@",LEN(E2)-LEN(SUBSTITUTE(E2,"\",)))))