How to extract file name from path?

I can't believe how overcomplicated some of these answers are... (no offence!)

Here's a single-line function that will get the job done:

Function getFName(pf)As String:getFName=Mid(pf,InStrRev(pf,"\")+1):End Function

Function getPath(pf)As String:getPath=Left(pf,InStrRev(pf,"\")):End Function

Examples:

Function file_name_only(file_path As String) As String

Dim temp As Variant

temp = Split(file_path, Application.PathSeparator)

file_name_only = temp(UBound(temp))

End Function

1. here you give your file name as input of the function
2. the split function of VBA splits the path in different portion by using "\" as path separator & stores them in an array named "temp"
3. the UBound() finds the max item number of array and finally assigns the result to "file_name_only" function

Hope this will be helpful.

I am using this function... VBA Function:

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

Now enter

=FunctionGetFileName(A1) in youe required cell.

or You can use these...

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