File Path to remove [SOLVED]

[webmax]

Hi,

I have the file path name C:UserswebmaxlDocumentsDelhi.xls


By using the formula i want only the file name which is Delhi without path and extension name.


kindly solve the above in excel formula as well as in using vba macro code.

 

[Luke M]

I'd suggest using the method described here:

http://chandoo.org/wp/2012/10/23/extract-file-name-from-full-path-using-formulas/


The article shows how to find file name and extension. From there, you can do a

=SUBSTITUTE(FileName,Extension,"")

to get just the basic name w/o extension.

One VBA method:

[pre]

Sub LastBit()
Dim textBits As Variant
Dim myPath As String
Dim fName As String
Dim shortName As String
myPath = "C:UserswebmaxlDocumentsDelhi.xls"

textBits = Split(myPath, "")
fName = textBits(UBound(textBits))
shortName = Left(fName, (InStrRev(fName, ".", -1, vbTextCompare) - 1))
MsgBox "File name is: " & shortName

End Sub

[/pre]

 

[webmax]

hi i want this output which is delhi in A2 Cell

 

[Luke M]

If output from formula, put formula in A2.

If output from VBA, add this line:

Range("A2").value = shortName

 

[webmax]

thanks

 

[shrivallabha]

Instead of Msgbox give the cell address:

[pre]

mypath = "C:UserswebmaxlDocumentsDelhis.xls"
myFile = Mid(mypath, InStrRev(mypath, "") + 1, InStrRev(mypath, ".") - InStrRev(mypath, "") - 1)
Range("A2").Value = myFile

[/pre]