使用单元格公式从文件路径获取父文件夹路径

发布于 2024-10-28 23:03:14 字数 404 浏览 1 评论 0原文

在 A 列中，我有 20000 行，其中包含文件名和文件路径

“C:\person\microsoft\ygkyg\mmddyy\文件名.xls”
“\server-41\performance\mmddyy\文件名.doc”
......
等等

在 B 列中，我只想获取父文件夹路径。

有人可以帮我计算公式吗？我尝试了这个，但它给了我文件名。

=MID(a1,FIND(CHAR(1),
    SUBSTITUTE(a1,"\",CHAR(1),LEN(a1)-LEN(SUBSTITUTE(a1,"\",""))))+1,LEN(a1))

原文

In column A I have 20000 rows with filename with file path

"C:\person\microsoft\ygkyg\mmddyy\filename.xls"
"\server-41\performance\mmddyy\filename.doc"
.....
etc.

In column B I just want to get the parent folder path.

Could someone help me with the formula? I tried this but it's giving me the file name.

=MID(a1,FIND(CHAR(1),
    SUBSTITUTE(a1,"\",CHAR(1),LEN(a1)-LEN(SUBSTITUTE(a1,"\",""))))+1,LEN(a1))

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

挽袖吟 2024-11-04 23:03:14

这有效。

=MID(A1,1,LEN(A1)-LEN(MID(A1,FIND(CHAR(1)),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE( A1,"\",""))))+1,LEN(A1))))

以上是我原来的答案。 Neil 稍微简化了表达式并将其发布为下面的注释：

=LEFT(A1,FIND("?",SUBSTITUTE(A1,"\","?",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

这利用了 ? 是一个事实路径中禁用字符，以便可以安全地使用 "?" 代替 CHAR(1) 作为地标，从而稍微提高可读性。此外，LEFT(A1,x) 等价于 MID(A1,1,x)，并且比 MID(A1,1,x) 短，因此使用 LEFT 是有意义的代码>.但最重要的是，此公式使用了 FIND，而不是使用 LEN 进行字符计数的第二层。这使得它更具可读性。

This works.

=MID(A1,1,LEN(A1)-LEN(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))))

The above was my original answer. Neil simplified the expression somewhat and posted this as a comment below:

=LEFT(A1,FIND("?",SUBSTITUTE(A1,"\","?",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

This takes advantage of the fact that ? is a forbidden character in paths so that "?" can safely be used instead of CHAR(1) as a placemark, thus improving readability a little bit. Also, LEFT(A1,x) is equivalent to, and shorter than MID(A1,1,x), so it makes sense to use LEFT. But most importantly, this formula makes use of FIND, instead of a second layer of counting characters using LEN. This makes it much more readable.

回复收藏 0 原文

小猫一只 2024-11-04 23:03:14

对于 2024 年遇到此问题的人，Excel 2019/365 及更高版本中提供了一种更简单的方法：

=TEXTBEFORE(A1,"\",-1)

For those coming to this problem in 2024, there's a much easier method available in Excel 2019/365 onward:

=TEXTBEFORE(A1,"\",-1)

回复收藏 0 原文

伤痕我心 2024-11-04 23:03:14

我没有足够的声誉来评论 Bendataclear 针对 Excel 2019/365 的解决方案，但如果单元格 A1 中没有文件路径，则可以使用以下公式：

=TEXTBEFORE(CELL("filename"),"\",-1)

I don't have enough reputation to comment on bendataclear's solution for Excel 2019/365, but if you don't have the file path in cell A1, you can use this formula: