Note: The other languages of the website are Google-translated. Back to English
登入  \/ 
x
or
x
註冊  \/ 
x

or

如何快速從Excel中的完整路徑中提取文件名?

假設您有一個包含文件路徑列表的工作表,現在您只想從每個路徑中提取文件名(最後反斜杠的右側),如下面的屏幕快照所示。 是否有任何快速的技巧來處理此任務?

使用Excel中的公式從完整路徑中提取文件名
使用用戶定義函數從完整路徑中提取文件名
使用VBA代碼從完整路徑中提取文件名


使用Excel中的公式從完整路徑中提取文件名

在Excel中,您可以使用以下公式快速從完整路徑中僅提取文件名。

選擇一個空白單元格,在其中輸入以下公式,然後按 Enter 鍵。

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

保養竅門:A1是要從中提取文件名的單元格,然後按Enter按鈕,然後拖動填充手柄以填充所需的範圍。 之後,從每個單元格提取文件名。

doc提取名稱1


使用用戶定義函數從完整路徑中提取文件名

使用以下用戶定義函數,您可以輕鬆快捷地獲取文件名。

1。 按住 ALT + F11 鍵打開 Microsoft Visual Basic for Applications窗口.

2。 點擊 插入 > 模塊,然後將以下代碼粘貼到 模塊窗口.

Function FunctionGetFileName(FullPath As String) As String
'Update 20140210
Dim splitList As Variant
splitList = VBA.Split(FullPath, "\")
FunctionGetFileName = splitList(UBound(splitList, 1))
End Function

3.其他 + Q 關閉鍵 Microsoft Visual Basic for Applications 窗口,然後返回工作表。 在空白單元格(例如B1)中,輸入以下公式,然後按 Enter 鍵。

=FunctionGetFileName(A1)

doc-extract-filenames1

然後再次選擇單元格B1,將填充手柄拖到您要應用此公式的範圍,並且所有文件名都已從完整路徑中提取出來,如下所示:

doc-extract-filenames1


使用VBA代碼從完整路徑中提取文件名

除了用戶定義函數外,VBA代碼還可以幫助您提取文件名。 請這樣做:

1。 按住 ALT + F11 鍵打開 Microsoft Visual Basic應用程序窗口.

2。 點擊 插入 > 模塊,然後將以下代碼粘貼到“模塊窗口”中。

Sub GetFileName()
'Update 20140210
Dim Rng As Range
Dim WorkRng As Range
Dim splitList As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    splitList = VBA.Split(Rng.Value, "\")
    Rng.Value = splitList(UBound(splitList, 1))
Next
End Sub

3。 然後按 F5 鍵運行此代碼,然後選擇要從中提取文件名的範圍,請參見屏幕截圖:

doc-extract-filenames1

4。 然後點擊 OK,文件名已從選擇中提取出來,如下所示:

備註:使用此VBA代碼,原始數據將被銷毀,因此您應在應用此代碼之前複製一個數據。


最佳辦公效率工具

Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及您以前使用過的任何東西; 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 超級公式欄 (輕鬆編輯多行文本和公式); 閱讀版式 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不會丟失數據; 拆分單元格內容; 合併重複的行/列...防止細胞重複; 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 (所有單元格都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇...
  • 確切的副本 多個單元格,無需更改公式參考; 自動創建參考 到多張紙; 插入項目符號,複選框等...
  • 提取文字,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級濾鏡 (將過濾方案保存並應用於其他工作表); 高級排序 按月/週/日,頻率及更多; 特殊過濾器 用粗體,斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表; 將數據分割成多個工作表; 批量轉換xls,xlsx和PDF...
  • 超過300種強大功能。 支持Office / Excel 2007-2019和365。支持所有語言。 在您的企業或組織中輕鬆部署。 完整功能30天免費試用。 60天退款保證。
kte選項卡201905

Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 每天將您的工作效率提高50%,並減少數百次鼠標單擊!
officetab底部
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Rese · 1 years ago
    Very useful function. Is there a way to modify the script so it finds the filename if the path has either \ or / slashes?

    I have a spreadsheet with various hyperlinks where the underlying paths are either \ or / (back slash or forward slash) separates - I think its because some of the links to files were done just as bookmarks in Word originally, or to files on an internal doc server. Or maybe its because some path links are made with absolute vs relative path links?

    eg:

    ../../../../Documents/2ndQuarter/2019/standardcost_widget12345.pdf
    or
    \fileserver\factory23\Operations\Parts_Mgt\Documents\2ndQuarter\2019\standardcost_widget12345.pdf


    When I ran the getfilename function, it got all the filenames that were in paths with \ between directories or folders, but the links with / slashes were returned as is.
    I altered & added a second function that was similar but replaced the "\" in line 4 with "/" and called it forwardslashgetfilename and run it in a separate column after I run the first function.

    Running one function after another is not difficult, but I was curious if you can expand the code in splitList operation in line 4 to include both "\" or "/". I'm no VBA programmer but I tried splitList = VBA.Split(FullPath, "\" or "/") and it didn't work.

    Thoughts? I'm assuming its some simple syntax - I'm just clueless at this point... but I will start poking around the interwebs...

    Tks!
  • To post as a guest, your comment is unpublished.
    bob · 3 years ago
    The formula raises a #VALUE! error if the source cell has only a filename to begin with.. Embedding the entire formula in an IFERROR function solves this problem, e.g., =IFERROR(<orig formula>,A1)