Extract filename from path

Question

I need to extract the filename from a path (a string):

e.g., "C:\folder\folder\folder\file.txt" = "file" (or even "file.txt" to get me started)

Essentially everything before and including the last \

I've heard of using wildcards in place of Regex (as it's an odd implementation in VBA?) but can't find anything solid.

Cheers in advance.

You might do well to have a look at the VBA help for the 'FileSystemObject`, and its 'GetFileName` method... — jtolle, May 09 '11 at 13:01

score 26 · Answer 1 · answered May 09 '11 at 05:41

26

I believe this works, using VBA:

Dim strPath As String
strPath = "C:\folder\folder\folder\file.txt"

Dim strFile As String
strFile = Right(strPath, Len(strPath) - InStrRev(strPath, "\"))

InStrRev looks for the first instance of "\" from the end, and returns the position. Right makes a substring starting from the right of given length, so you calculate the needed length using Len - InStrRev

answered May 09 '11 at 05:41

kaveman

4,339
25
44

sorry I had to edit that: you need to use `Len(..) - InStrRev(..)` to get the difference in length from the whole string and the position of the last "\", which is the length of the substring you would like to take from the right. I tried this in Excel2007 and it gave me "file.txt" – kaveman May 09 '11 at 05:48
I added an extension of your solution that solves my exact scenario. Thanks for the help bud! – Matt Rowles May 11 '11 at 05:36
See my comment below on Matt Rowles's answer. – shlgug Dec 07 '17 at 20:59

score 7 · Accepted Answer · edited May 23 '17 at 11:33

7

Thanks to kaveman for the help. Here is the full code I used to remove both the path and the extension (it is not full proof, does not take into consideration files that contain more than 2 decimals eg. *.tar.gz)

sFullPath = "C:\dir\dir\dir\file.txt"   
sFullFilename = Right(sFullPath, Len(sFullPath) - InStrRev(sFullPath, "\"))
sFilename = Left(sFullFilename, (InStr(sFullFilename, ".") - 1))

sFilename = "file"

edited May 23 '17 at 11:33

Community

1
1

answered May 11 '11 at 05:35

Matt Rowles

7,721
18
55
88

To only remove the last extension (in filenames with more than one dot), use InStrRev -- i.e. change you last line to `sfilename = Left(sFullFilename, (InStrRev(sFullFilename, ".") - 1))` – shlgug Dec 07 '17 at 20:57

score 5 · Answer 3 · edited Oct 08 '13 at 06:27

5

I was looking for a solution without code. This VBA works in the Excel Formula Bar:

To extract the file name:

=RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,"\","~",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

To extract the file path:

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

edited Oct 08 '13 at 06:27

Anirudh Ramanathan

46,179
22
132
191

answered Aug 23 '13 at 15:22

live-love

48,840
22
240
204

You might substitute ~ for any special character, if your file has ~ in it. – live-love Sep 04 '13 at 21:14

score 2 · Answer 4 · answered Sep 14 '14 at 18:35

`You can also try:

Sub filen() Dim parts() As String Dim Inputfolder As String, a As String 'Takes input as any file on disk Inputfolder = Application.GetOpenFilename("Folder, *") parts = Split(Inputfolder, "\") a = parts(UBound(parts())) MsgBox ("File is: " & a) End Sub

This sub can display Folder name of any file

score 2 · Answer 5 · answered Apr 25 '18 at 05:42

Here's simpler method: a one-line function to extract only the name — without the file extension — as you specified in your example:

Function getName(pf):getName=Split(Mid(pf,InStrRev(pf,"\")+1),".")(0):End Function

...so, using your example, this:

MsgBox getName("C:\folder\folder\folder\file.txt")

returns:

"file"

For cases where you want to extract the filename while retaining the file extension, or if you want to extract the only the path, here are two more single-line functions:

Extract Filename from x:\path\filename:
Function getFName(pf)As String:getFName=Mid(pf,InStrRev(pf,"\")+1):End Function
Extract Path from x:\path\filename:
Function getPath(pf)As String: getPath=Left(pf,InStrRev(pf,"\")): End Function
Examples:

^(Source)

score 1 · Answer 6 · edited Sep 11 '14 at 17:12

1

I used kaveman's suggestion successfully as well to get the Full File name but sometimes when i have lots of Dots in my Full File Name, I used the following to get rid of the .txt bit:

FileName = Left(FullFileName, (InStrRev(FullFileName, ".") - 1))

edited Sep 11 '14 at 17:12

Technext

7,887
9
48
76

answered Sep 11 '14 at 16:36

Alan Elston

89
1
4
11

It was intended as a thanks and an alternative answer. It was my very first time here, so sorry if I did something wrong? – Alan Elston Sep 11 '14 at 18:37

score 1 · Answer 7 · answered May 09 '11 at 05:38

1

Using Java:

String myPath="C:\folder\folder\folder\file.txt";
System.out.println("filename " +  myPath.lastIndexOf('\\'));

answered May 09 '11 at 05:38

Luixv

8,590
21
84
121

score 0 · Answer 8 · answered Mar 13 '17 at 17:30

You can use a FileSystemObject for that.

First, include a reference for de Microsoft Scripting Runtime (VB Editor Menu Bar > Tools > References).

After that, you can use a function such as this one:

Function Get_FileName_fromPath(myPath as string) as string
    Dim FSO as New Scripting.FileSystemObject

    'Check if File Exists before getting the name
    iF FSO.FileExists(myPath) then
        Get_FileName_fromPath = FSO.GetFileName(myPath)
    Else
        Get_FileName_fromPath = "File not found!"
    End if
End Function

File System Objects are very useful for file manipulation, especially when checking for their existence and moving them around. I like to use them early bound (Dim statement), but you can use them late bound if you prefer (CreateObject statement).

Harry S · Answer 9 · 2021-02-22T22:28:38.070

'[/vba] ' Keep It Simple ' .. why use FileSystemObject or Split when Left and Mid will do it ' the FSO has some 33 Subs or Functions that have to be loaded each time it is created. ' and needs the file to exist ... yet is only a bit slower

... under twice time.. some good code in FSO ' conservation is good .. spare a few electrons. ????... save a few millionths of a sec

'Also ' .. why the format of a function that we all seem to use like ' ' .. Function GetAStr(x) as string

' dim extraStr as string

' a lot of work with extraStr.. ' that could have been done with the string variable GetAStr already created by the function

' then .. GetAStr=extraStr to put it in its right place ' .. End Function

Function GetNameL1$(FilePath$, Optional NLess& = 1)

' default Nless=1 => name only ' NLess =2 => xcopya.xls xcopyb.xls xcopy7.xlsm all as xcopy to get find latest version ' Nless = - 4 or less => name with name.ext worka.xlsm

GetNameL1 = Mid(FilePath, InStrRev(FilePath, "") + 1)

GetNameL1 = Left(GetNameL1, InStrRev(GetNameL1, ".") - NLess)

End Function

Function LastFold$(FilePath$)

LastFold = Left(FilePath, InStrRev(FilePath, "") - 1)

LastFold = Mid(LastFold, InStrRev(LastFold, "") + 1)

End Function

Function LastFoldSA$(FilePath$)

Dim SA$(): SA = Split(FilePath, "")

LastFoldSA = SA(UBound(SA) - 1)

End Function

[<vba]

Extract filename from path

9 Answers9

Extract Filename from `x:\path\filename`:

Extract Path from `x:\path\filename`:

Linked

Related

Extract filename from path

9 Answers9

Extract Filename from x:\path\filename:

Extract Path from x:\path\filename:

Linked

Related

Extract Filename from `x:\path\filename`:

Extract Path from `x:\path\filename`: