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")))))))
注意一下异常情况,如单元格为空,需要加个判断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,"\",)))))
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· 上周热点回顾(2.17-2.23)
· 如何使用 Uni-app 实现视频聊天(源码,支持安卓、iOS)
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)