Extract file name from a path in Excel using formula and VBA

Welcome to Logically Proven blog.

This post demonstrates how to extract a file name from a path using Excel and VBA.

The following are the ways to extract a file name from a path. You can use any one of these methods to extract a file name.

1. Using Excel Formula
2. Using VBA code
3. Using File System Object (VBA)

Using Excel Formula:

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

Consider range  A1 contains a path "C:\Test\file.txt".
Output of this formula is "file.txt"

Using VBA code:

Sub extractfilename()

Dim sFullPath As String
Dim sFullFilename As StringUsin
Dim sFilename As String

    sFullPath = Sheet1.Range("A1").Value
    If sFullPath = "" Then
        Exit Sub
    End If
    sFullFilename = Right(sFullPath, Len(sFullPath) - InStrRev(sFullPath, "\"))
    sFilename = Left(sFullFilename, (InStr(sFullFilename, ".") - 1))
    Sheet1.Range("B1").Value = sFilename

End Sub

3. Using File System Object (VBA)

One more way to get the file name is using file system object.

If you want to use file system object, first we have to add reference to the library "Microsoft Scripting Runtime".

To add, in code window, Select Tools -> Select References -> Search for the library in the list -> check the button "Microsoft Scripting Runtime" -> click OK.

Then try the following code to extract the file name from a path. Make sure the path is valid else you will receive an error message. File system objects checks if path exists or not. You can add a condition to check if the files exists or not before extracting using fso.FileExists(path) returns a boolean.

Sub ExtractFileName()

     Dim fso as new FileSystemObject
     Dim fileName As String

     fileName = fso.GetFileName("C:\Test\file.txt")
     set fso = Nothing

End Sub

These are some ways to extract file name from a path.


Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.


Logically Proven
Learn, Teach, Share

Karthik Byggari

Author & Editor

Computer Science graduate, Techie, Founder of logicallyproven, Love to Share and Read About pprogramming related things.