公式找到最后一个“ \“在文件路径中,然后删除所有 [英] Formula to find the last " \ " in a filepath and remove all after that
问题描述
有没有一个公式可以让我在文件路径中的最后一个\之后修剪?
提前感谢任何帮助!
首先,您的问题在 superuser.com 。
您可以使用 LEFT
与 FIND
和 SUBSTITUTE
...和其他几个人:
= LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,\,CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,\,))) )-1)
LEN(A1)-LEN(SUBSTITUTE(A1, \,)
基本上给出了字符串中 \
的数量。
SUBSTITUTE(A1,\,CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,\,))
此部分用最后一个 \
替换为一个名为 CHAR(1)
的字符。
然后,使用 FIND
获取此角色的位置,减去1以删除该找到的角色的位置,为 LEFT
(包括比喻和字面上)与您需要的部分。
如果您需要最后一个反斜杠,请删除 -1
。
So I have an excel task that involves taking filepaths (C:\foo...) and getting just the path (that is, removing the actual file name from the path). I can't seem to get the SEARCH/FIND function to work since it is always finding the first "\" in the file path (after the drive designation) and only removes 3 or so characters.
Is there a formula that will allow me to trim after the last "\" in the filepath?
Thanks in advance for any help!
First of all, your question would be better off on superuser.com.
You can use LEFT
, with FIND
and SUBSTITUTE
... and a couple others:
=LEFT(A1, FIND(CHAR(1), SUBSTITUTE(A1, "\", CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1, "\", ""))))-1)
LEN(A1)-LEN(SUBSTITUTE(A1, "\", "")
basically gives the number of \
in the string.
SUBSTITUTE(A1, "\", CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1, "\", ""))
This part substitute the last \
by a character called CHAR(1)
.
Then, use FIND
to get the position of this character and minus 1 to remove that found character's position, to be LEFT
(both figuratively and literally) with the part you need.
If you need the last backslash, remove the -1
.
这篇关于公式找到最后一个“ \“在文件路径中,然后删除所有的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!