Extract filename from path

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))))

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"

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

`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