Excel公式 提取文件路径后缀

C#

浏览数:192

2019-6-14

我们在代码中获取一个文件路径的后缀,是一个很简单的事.

如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")))))))

注意一下异常情况,如单元格为空,需要加个判断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,"\",)))))

 

作者:唐宋元明清2188