Sub ShowFileName()
Dim FilePath As String, FileName As String
FilePath = "X:\users\slef\2015\1 June\my doc 5.xls"
FileName = Mid$(FilePath, InStrRev(FilePath, "\") + 1)
MsgBox FileName
End Sub
Rather than use their formula, I think I would use this one instead...There's an easy guide at this page Extract Filenames from Filepath in Excel
Sub ExtractfilePath()
ActiveSheet.Range("A1:A10").Replace "*\", "", xlPart
End Sub
Actually, since I am pretty sure Replace only works on the UsedRange, I would write the macro this way instead (ActiveSheet is the default, so I have eliminated it also)...Also, instead of their multi-line subroutine, I think I would just use the coded equivalent of the manual method they posted...
Code:Sub ExtractfilePath() ActiveSheet.Range("A1:A10").Replace "*\", "", xlPart End Sub
Sub ExtractfilePath()
Columns("A").Replace "*\", "", xlPart
End Sub
That is definitely a better solution! ThanksRather than use their formula, I think I would use this one instead...
=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",300)),300))