asked on

Extract filename from full path

Experts

I have a variable which contains the full file path for a file, example below:
C:\Documents and Settings\leecett\My Documents\output.txt

I want to extract the filename (without the file extension).

Note, the filename may contain other full stops / periods (e.g. output.one.txt), but will always have a file extension of .txt.

So I have a variable:
strFilePath = C:\Documents and Settings\leecett\My Documents\output.one.txt
I want a variable:
strFileName = output.one

Thanks leecett

jppinto

Assuming that the file name/path is in cell A1, and down the column, put this on cell B1 and copy down:

=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1,"/","|",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

jppinto

SOLUTION

nike_golf

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

B34RDY

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

jppinto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MSmax

also try:

Dim temp As String, strFileName As String

temp = split(strFilePath, "\", vbBinaryCompare)
strFileName = Ubound(temp())

MSmax

now with missing comma

Dim temp As String, strFileName As String

temp = split(strFilePath, "\", ,vbBinaryCompare)
strFileName = Ubound(temp())

leecett

ASKER

Thanks to you all for your advice and solution, I have developed the following:

Sub findfilename()
Dim strFilePath As String
Dim strFileName As String
strFilePath = "C:\Documents and Settings\leecett\My Documents\output.one.txt"""
strFileName = Mid(strFilePath, (InStrRev(strFilePath, "\")) + 1, (InStrRev(strFilePath, ".") - (InStrRev(strFilePath, "\") + 1)))
End Sub

Many thanks

leecett